One Star

Problem with tPostgresqlOutput

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
16 REPLIES
Employee

Re: Problem with tPostgresqlOutput

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

Re: Problem with tPostgresqlOutput

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

Re: Problem with tPostgresqlOutput

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

Re: Problem with tPostgresqlOutput

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

Re: Problem with tPostgresqlOutput

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

Re: Problem with tPostgresqlOutput

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

Re: Problem with tPostgresqlOutput

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

Re: Problem with tPostgresqlOutput

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

Re: Problem with tPostgresqlOutput

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

Re: Problem with tPostgresqlOutput

Did you enter a table name in tPostgresqlOutput properties ?

Yes fortunately but thank you for your help.

are you located france, bordeaux ?

yes Smiley Wink

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

Best regards
Olivier
One Star

Re: Problem with tPostgresqlOutput

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"."" ...).
One Star

Re: Problem with tPostgresqlOutput

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 Smiley Wink )




Best regards
Olivier
One Star

Re: Problem with tPostgresqlOutput

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

Re: Problem with tPostgresqlOutput

Smiley Happy Smiley Happy Smiley Happy Smiley Happy Smiley Happy Smiley Happy Smiley Happy Smiley Happy Smiley Happy Smiley Happy Smiley Happy Smiley Happy
One Star

Re: Problem with tPostgresqlOutput

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

Re: Problem with tPostgresqlOutput

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.