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

Viewed 1561

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.

1 Answers

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

Related