topic Re: Problem with tPostgresqlOutput in Talend Studio

Problem with tPostgresqlOutput

_AnonymousUser — Sat, 16 Nov 2024 14:36:02 GMT

Hello.
I'm actually using Talend to compare this product with CloverETL.
In this goal, I'm doing some tests with component tPostgresqlOutput but I always receive this message while inserting in PostgreSQL :
Exception in component tPostgresqlOutput_1
org.postgresql.util.PSQLException: ERROR: zero-length delimited identifier at or near """"
...
However, if I return data into flat file there is no problem...
PS : My connection is well configured.

Can somebody help me?
Olivier

Re: Problem with tPostgresqlOutput

Anonymous — Wed, 13 Jun 2007 01:30:47 GMT

Hello,
Can you give us more details on your schema and the line of data not inserted ?
Best regards,
Michaël.

Re: Problem with tPostgresqlOutput

_AnonymousUser — Wed, 13 Jun 2007 09:05:02 GMT

I tried to put data from a flat file to a postgres table.
For the components, a "tFileInputDelimited" with schema ("firstname" in String, "name" in String and "num" in Integer) linked with a row Main to a "tPostgresqlOutput" having the same schema.
The key is on the "num" column.
In my database I get my table with the columns ("firstname" in text, "name" in text and "num" in integer).
While running I get this error message :
"Exception in component tPostgresqlOutput_1
org.postgresql.util.PSQLException: ERROR: zero-length delimited identifier ..."
None of my lines was inserted in my table.
Thanks for help.
Regards.

Re: Problem with tPostgresqlOutput

Anonymous — Thu, 14 Jun 2007 13:45:06 GMT

This postgreSQL error happens when an empty string ("") is provided. Nevertheless I was unable to reproduce this issue with empty values for firstname or name, as it should be handled by jdbc. What is your version of postgres, and could you post a sample of your input file ?
Best regards,
Jeremie.

Re: Problem with tPostgresqlOutput

Anonymous — Thu, 14 Jun 2007 13:58:05 GMT

hi,
set the commit to one for the output table so you can see where is the probleme in your text file (it's the line after the last line insert in your DB table)
maybe a null value for num field

Re: Problem with tPostgresqlOutput

_AnonymousUser — Thu, 14 Jun 2007 16:36:13 GMT

Thank you for these answers.
I thought too this error was caused by a null value into my data file or an invisible character so I tested to launch a new graph which inserts data provided by a PostgreSQL table to another table (just a copy with one row) but I have the same error...
Best regards
Olivier

Re: Problem with tPostgresqlOutput

Anonymous — Thu, 14 Jun 2007 17:12:35 GMT

What version of postgres, TOS ?
Java or Perl Project ?
Can you post a Screenshot of the job ant of the TMAP ?

Re: Problem with tPostgresqlOutput

_AnonymousUser — Fri, 15 Jun 2007 08:56:55 GMT

Hello,
here are these images :

job :

Tmap :

Schemas :

I try to link my frst two tables ("bordeaux" & "bordeaux2") with Tmap to load table "bordeaux3".but I have an error :
...ERROR: zero-length delimited identifier at or near """"...
But if I replace my table "bordeaux3" by a flat file it runs...
PS: My version of Postgres is 8.2.3 and TOS is V2.1.0M1
Best regards
Olivier

Re: Problem with tPostgresqlOutput

Anonymous — Fri, 15 Jun 2007 10:03:50 GMT

Did you enter a table name in tPostgresqlOutput properties ?
Best regards,
Jeremie.

Re: Problem with tPostgresqlOutput

Anonymous — Fri, 15 Jun 2007 10:18:39 GMT

postgres pbm i think,
is input and output are on the same DB, same schema ?
if not, check the codage DB to see if it's different.
may be a post on postgres forum can help you more !!
are you located france, bordeaux ?

Re: Problem with tPostgresqlOutput

_AnonymousUser — Fri, 15 Jun 2007 10:43:48 GMT

Did you enter a table name in tPostgresqlOutput properties ?

Yes fortunately but thank you for your help.

are you located france, bordeaux ?

yes

Well, I think too this is a problem with Postgres version...

Best regards
Olivier

Re: Problem with tPostgresqlOutput

Anonymous — Fri, 15 Jun 2007 10:53:25 GMT

When I print SQL requests, I get (with empty strings for prenom and / or nom) :

INSERT INTO "test"."output1" ("id","prenom","nom") VALUES (1,titi,toto)
INSERT INTO "test"."output1" ("id","prenom","nom") VALUES (2,NULL,NULL)
INSERT INTO "test"."output1" ("id","prenom","nom") VALUES (3,titi,NULL)

I only see 2 ways to get this error : either schema or table property is not filled (requests would look like INSERT INTO ""."output1" ... or INSERT INTO "test"."" ...).

Re: Problem with tPostgresqlOutput

_AnonymousUser — Fri, 15 Jun 2007 13:25:43 GMT

I only see 2 ways to get this error : either schema or table property is not filled (requests would look like INSERT INTO ""."output1" ... or INSERT INTO "test"."" ...).

Yes I know but values are not null and shemas are correctly filled because when I put a tFileOutputDelimited instead of a tPostgresqlOutput, I don't have this error any longer and my file contains the good values. This is why I think this is a problem with version/configuration of Postgres.
Here is the contents of my table (no error possible with values )

Best regards
Olivier

Re: Problem with tPostgresqlOutput

_AnonymousUser — Thu, 21 Jun 2007 09:03:01 GMT

Hello,
I finally found the problem.
In fact, in a database connection edition, a schema have to be indicated.
In a postgres database, every table is created in a default schema "public".
It was this default schema that was missing in my configuration.
Thanks for your help.

Re: Problem with tPostgresqlOutput

Anonymous — Thu, 21 Jun 2007 09:26:33 GMT

Re: Problem with tPostgresqlOutput

_AnonymousUser — Wed, 03 Sep 2008 20:57:49 GMT

I am still getting the same error, inspite of specifying the schema and everything.. can anyone help.

Re: Problem with tPostgresqlOutput

_AnonymousUser — Fri, 21 Nov 2014 06:54:42 GMT

Hi
Here I am having a situation.
I am having a talend job :
tpostgresqlInput -> tmap -> tpostgresqloutput
in tpostgresqlinput : select id,name from employee;
tpostgresql output : it is employee_staging table with the below information
id,name,created_time,updated_time
created_time i am always keeping as the current time in the tmap.
Now the requirement is whenever there is a update then only i need to stamp the updated_time.
tpostgresqloutput:
action on table : insert or update
So how can i differentiate between insert and update and stamp the updated_time to current time whenever there is a update.