Column is of type timestamp without time zone but expression is of type character varying : Nifi

Question

I am trying to insert CSV records to Postgres database using nifi.

Sample csv file :

timenow,location
'2019-10-21 15:13:11','colombo'

When nifi scenario runs, it gives following error

Error column 'timenow' is of type timestamp without time zone but expression is of type character varying

If I use normal insert into conditions (timenow,location) values ('2019-10-21 15:13:11','colombo');

No errors values inserted to table. Do I have to change nifi configurations to add this to table?

I have changed Nifi CSVReader -> Timestamp format YYYY-MM-DD HH:MM:SS

EDIT: After first comment Nifi generated query is : INSERT INTO public.conditions (timenow, location) VALUES ('2019-10-21 15:13:11','colombo')

I have used this with timescaledb terminal and it inserted the record.

Bilberry

Can you see the SQL Nifi is executing against your database? Because if you take that CSV file and try to COPY it into TImescaleDB it is ok. – Tiber

@BlagojAtanasovski INSERT INTO public.conditions (timenow, location) VALUES ('2019-10-21 15:13:11','colombo'), if I run this on my timescale terminal, it inserts a record. – Bilberry

Answer 1

It was a simple fix in nifi side.

In DBCPConnectionPool -> Database URL -> jdbc:postgresql://localhost:5432/databaseName?stringtype=unspecified

Answer was given by ajaytigga0210 on Facing issues with Date and Timestamp Columns

Bilberry