topic Re: How to insert null value to Postgres/Redshift timestamp column in Talend Studio

How to insert null value to Postgres/Redshift timestamp column

Anonymous — Mon, 04 May 2015 23:05:06 GMT

Hello All,
I have a problem with inserting null value into Redshift (postgres) timestamp column.
I got 'Batch entry 0 INSERT INTO "public"."tester" ("id","txt","var","data") VALUES (1,ala ma kota,22,NULL) was aborted. Call getNextException to see the cause.' error.
After I disabled batch mode it was converted into: 'ERROR: Specified types or functions (one per INFO message) not supported on Redshift tables.ERROR: current transaction is aborted, commands ignored until end of transaction block' error.
I've add tJava component which print my query: 'INSERT INTO "public"."tester" ("id","txt","var","data") VALUES (2,'ala ma kota',33,null)' and:
if I copy paste this exact query into my redshift client and execute it, it works perfect 😕
If I replace empty value with TalendDate.GetCurrentDate() it works perfect,
If I do something like 'Relational.ISNULL(row1.when_items_updated)?null:row1.when_items_updated ', it's not working.
environment: "Talend Open Studio for Data Integration" Version: 5.6.1 Build id: V5.6.1_20141207_1530
PS. this is my first question if I missed some vital information, please dont hesitate to ask.
my screenshots:

this is with TalendDate.getCurrentDate() replacement. no error
no data for date (null)
mapping

Re: How to insert null value to Postgres/Redshift timestamp column

Anonymous — Tue, 05 May 2015 05:52:16 GMT

Hi,
I think you have specified batch insert size, due to that you are getting this error.
To solve your issue remove batch size in advanced settings of postgresoutput component.
-Bhanu

Re: How to insert null value to Postgres/Redshift timestamp column

Anonymous — Tue, 05 May 2015 08:37:21 GMT

Hello Bhanu,
it's already non batch insert, thats why I am able to read correct error description.

Re: How to insert null value to Postgres/Redshift timestamp column

Anonymous — Tue, 05 May 2015 09:42:18 GMT

Hello Guys..
there is no error after all.
Looks like if you want to insert null value into redshift you HAVE TO use redshift output component instead of postgres :/
I used to pick postgres one because of my habits but looks like they have some differences in handling specific cases.
thanks!