I need to insert the result of an SQL query into a Postgres table. For that I use ExecuteSQL("Use Avro Logical Types" true) and PutDatabaseRecord("StatementType" INSERT, "Record Reader" AvroReader) processors. The insert doesn’t work because Nifi converts the date to this number: '1322683200000' and the column in the destination table is of type date.

I suppose I should either add the "UpdateRecord" processor between "ExecuteSQL" and "PutDatabaseRecord" processors or use "Data Record Path" property in the "PutDatabaseRecord" processor. But I can't find an example of configuring the UpdateRecord processor or filling the "Data Record Path" property

CodePudding user response：

I tried to do the same process on my side with a dummy select current_date as value; in the sql execute processor and then passing the same over to insert the data into PostgresDB.

The flow was able to insert the date value into the sample table that I created for testing. Can you try the same and see if you have any issues or provide some samples of data and how you are building the data flow.

CodePudding user response：

Insert started to work when I put columns in sql query in the same order as in destination table.

I thought that Nifi should match columns by names