topic Re: postgresql format problems for insert in Talend Studio

postgresql format problems for insert

Anonymous — Sat, 16 Nov 2024 11:52:19 GMT

I'm new to Talend (downloaded TOS_DI-r104014-V5.3.1 last week) and trying to use it with Postgresql 10.12.0.2.
I read in a csv file using tFileInputDelimited, map the column names to my database table using tMap, and then attempt to insert into my table using tPostgresqlOutput. When I run in Traces Debug I can see my data moving through properly.
But when I try running it, I am getting error messages when trying the insert as it is not formatted correctly. This is what is shown in the error window on the Run tab (one row example as there are thousands):
Batch entry 0 INSERT INTO "dwh"."all_data" ("timestamp","action","session_id","src_user_id","src_community_id") VALUES (2013-03-08 13:37:51.000000 -05:00:00,userData,bEjseZl0si85ANN1njysOf+9,,) was aborted. Call getNextException to see the cause.
The problem is that the first three columns are flagged as date for timestamp, and string for the next 2, and the last 2 are integer, but are empty. I would expect to see it with this format:
INSERT INTO "dwh"."all_data" ("timestamp","action","session_id","src_user_id","src_community_id")
VALUES (?2013-03-08 13:37:51.000000 -05:00:00?,?userData?,?bEjseZl0si85ANN1njysOf+9?,NULL,NULL)
I don't have the single quotes around the first three columns, and then the word NULL is missing from the 4th and 5th columns.
I'm not sure how to correct this but it is very frustrating as I want to do more things with my data once I get it into the database.

Re: postgresql format problems for insert

Anonymous — Wed, 16 Oct 2013 06:10:46 GMT

first check constraint on Postgresql table, because most of the time it throws same error for null value insertion or primary key violation errors. hope this may solve your problem,

Re: postgresql format problems for insert

Anonymous — Wed, 16 Oct 2013 16:04:26 GMT

My table I'm trying to insert into is empty. There are no constraints on the table, not even primary key. When I attempt to insert the reformatted sql statement it goes in fine when connecting to the database correctly. How do I make Talend format the insert statement so that it will be accept by Postgresql?

Re: postgresql format problems for insert

Anonymous — Thu, 17 Oct 2013 04:53:43 GMT

i think you have to do those adjustment in using tMap. with the help of expression builder and StringHandling routine, there are several string manipulation function like surrounding string with single quote or double quote and many more. please check that routine it may help to sort all the issue.

Re: postgresql format problems for insert

Anonymous — Thu, 17 Oct 2013 16:09:20 GMT

I'm currently evaluating Talend to see if it will meet the needs of my company. If it says that it works with Postgresql, I expect that it will format the sql statements in a way that Postgresql needs them. I've also been looking at one of Talend's competitors, and after setting it in one place I was able to insert the same delimited file into the same database table with no issues. I really want to like Talend as it seems like a great product and I like the open source nature of it. But without easy database support I can't recommend it. I don't want to be writing complicated formatting strings for something that should just work. Is there a setting somewhere to tell Talend to enclose strings in single quotes, and use the word NULL for null values? Please help.

Re: postgresql format problems for insert

Anonymous — Fri, 24 Jul 2015 07:26:04 GMT

Hi,
I am also facing the same problem.MY insert statement gets aborted after running for 30 mins.Please help.

Re: postgresql format problems for insert

Anonymous — Wed, 09 Nov 2016 13:25:38 GMT

almost a year, and no one answer, yet not even fixed the component?
Here is my case. I Have tGreenplumGPLoad, and purposely using this to make the data inserting faster.
GPLoad technically put the data into CSV and reload it into target GP table. Suddenly one of the inserting process faced this problem.
I believe the problem is because the NULL value. especially the INT(or any numeric) & Date column. The value is "" instead of NULL, and the job will die. But I'm curious, some other job working fine with the GPLoad.
Anyway I'm not gonna put string handling for the null things since there are more then 100 columns.
Is there any solution for this problem?
Please your help
---------------------------------------
EDIT:
Sorry, this was my mistake. I forgot to put the Parameter "NULL_AS" in the Advanced Settings tab of tGreenPlumGPLoad.
Worked well after that.

Thanks

Re: postgresql format problems for insert

Anonymous — Thu, 10 Nov 2016 08:54:51 GMT

Hi buby.uguy,

Sorry, this was my mistake. I forgot to put the Parameter "NULL_AS" in the Advanced Settings tab of tGreenPlumGPLoad.
Worked well after that.

Great your issue is fixed. Feel free to let us know if there is any error in your job.
Best regards
Sabrina

Re: postgresql format problems for insert

Anonymous — Sat, 24 Dec 2016 18:48:30 GMT

I am getting the same error when exporting from a mysqlinput component to a postgresqloutput component. The connection setting ahs the string delemiter set to ' (single quote). Any suggestions where to look. Swapping out the postgresqloutput component for a text file works fine.
Ok figured it out - seems it was a problem with the table definition.

Re: postgresql format problems for insert

Anonymous — Mon, 26 Dec 2016 03:31:52 GMT

Hi mxc4,

I am getting the same error when exporting from a mysqlinput component to a postgresqloutput component. The connection setting ahs the string delemiter set to ' (single quote). Any suggestions where to look. Swapping out the postgresqloutput component for a text file works fine.

Is this issue caused by your table definition? Feel free to post your issue here.
Best regards
Sabrina

Re: postgresql format problems for insert

Anonymous — Mon, 04 Mar 2019 21:03:24 GMT

Hey Did you found anything here, i am facing similar issue where the insert being sent without the quotes for strings and because of that Insert fails!

@sclccangmailcom wrote:
My table I'm trying to insert into is empty. There are no constraints on the table, not even primary key. When I attempt to insert the reformatted sql statement it goes in fine when connecting to the database correctly. How do I make Talend format the insert statement so that it will be accept by Postgresql?