One Star

How to insert null value to Postgres/Redshift timestamp column

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
3 REPLIES
Two Stars

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

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
One Star

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

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

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

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!