Fluentd Inserting NULL Value in PostgreSQL Column Not Mapped in Table Configuration

[KanjiVidyasagar]

Hi Support Team,

I am currently using Fluentd to insert log data into a PostgreSQL table (metrics_apis_received). However, I have encountered an issue where the insert_time column in the PostgreSQL table is being populated with NULL instead of the expected DEFAULT now() value when data is inserted.

Here is the current setup for the Fluentd configuration and PostgreSQL table:

Fluentd Configuration (Snippet):

<filter metrics_apis_received>
  @type record_transformer
  enable_ruby
  <record>
    log_time_metrics_apis_received ${record["log_time_metrics_apis_received"]}
    company_metrics_apis_received ${record["company_metrics_apis_received"]}
    method_metrics_apis_received ${record["method_metrics_apis_received"]}
    path_metrics_apis_received ${record["path_metrics_apis_received"]}
    source_metrics_apis_received ${record["source_metrics_apis_received"]}
    count_metrics_apis_received ${record["count_metrics_apis_received"]}
  </record>
</filter>

<filter metrics_apis_received>
  @type grep
  <exclude>
    key path_metrics_apis_received
    pattern \/api\/v1\/heartbeat
  </exclude>
</filter>

<match metrics_apis_received>
  @type sql
  adapter postgresql
  host xxxxxxxxxx
  port xxxxxxx
  database xxxxxxxxxxxxxxx
  username xxxxxxxxxxxx
  password xxxxxxxxxxxx
  <table>
    table metrics_apis_received
    column_mapping log_time_metrics_apis_received:log_time, company_metrics_apis_received:company, method_metrics_apis_received:method, path_metrics_apis_received:path, source_metrics_apis_received:source, count_metrics_apis_received:count
  </table>
  <buffer>
    chunk_limit_size 16MB
    flush_mode interval
    flush_interval 2s
  </buffer>
</match>

PostgreSQL Table Schema:

CREATE TABLE IF NOT EXISTS public.metrics_apis_received
(
    log_time timestamp without time zone NOT NULL,
    company text COLLATE pg_catalog."default",
    method text COLLATE pg_catalog."default",
    path text COLLATE pg_catalog."default",
    source text COLLATE pg_catalog."default",
    count integer,
    insert_time timestamp without time zone DEFAULT now()
);

Issue:
The insert_time column is set to NULL on insert, which is not expected, as it has a default value of now() in the PostgreSQL schema.
When inserting data manually into the table, insert_time is correctly populated with the current timestamp (now()).

Expected Behavior:
The insert_time column should automatically use the default now() value when no value is provided during insert, without needing to map it explicitly in the Fluentd configuration.

Can you please assist in resolving this issue, or suggest the best approach to ensure the insert_time column is populated correctly with the default timestamp (now())?

Thank you for your assistance.

Best regards,
Kanji Vidyasagar

[KanjiVidyasagar]

sample log from Postgres:

2025-01-17 12:37:15 UTC [2509131]: user=postgres,db=metrics_db,app=/fluentd/vendor/bundle/ruby/3.1.0/bin/fluentd,client=10.224.0.15LOG: execute : INSERT INTO "metrics_apis_received" ("log_time","company","method","path","source","count","insert_time") VALUES ('2025-01-17 12:37:13.069000','IDFC','GET','/api/v2/common/summary','DASHBOARD',1,NULL),('2025-01-17 12:37:13.276000','IDFC','GET','/api/v2/common/config/alertsetting','DASHBOARD',1,NULL)

[PremSahooESL]

If 'column_mapping' does not include certain columns, why Fluentd is sending NULL value by default for those columns?
In our case 'insert_time' column. which is not mentioned on below list of columns.

column_mapping log_time_metrics_apis_received:log_time, company_metrics_apis_received:company, method_metrics_apis_received:method, path_metrics_apis_received:path, source_metrics_apis_received:source, count_metrics_apis_received:count

Is there a way to configure Fluentd, so that it can get rid of NULL values for 'insert_time' column?