4

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.

Sachith Muhandiram
  • 2,819
  • 10
  • 45
  • 94
  • 1
    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. – Blagoj Atanasovski Oct 21 '19 at 11:52
  • @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. – Sachith Muhandiram Oct 21 '19 at 12:02

1 Answers1

6

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

Sachith Muhandiram
  • 2,819
  • 10
  • 45
  • 94