topic Re: PostgreSQL output Update or insert error. in Talend Studio

PostgreSQL output Update or insert error.

_AnonymousUser — Tue, 12 Aug 2014 09:54:51 GMT

I'm getting an error when using Insert or update or
Update or insert in tPostgreSQLUpdate with all table
columns marked as keys in the schema. The situation
is pretty much the same as here:
If one or more columns are not keys, the problem disappears.
I'm running TOS for Big Data (5.6.0.M1-r118307).
With Insert or update I get error:

ERROR: syntax error at or near "WHERE"
  Position: 44Exception in component tPostgresqlOutput_2
org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
    at - -

and with Update or insert I get:

ERROR: syntax error at or near "WHERE"
  Position: 44ERROR: syntax error at or near "WHERE"
  Position: 44ERROR: syntax error at or near "WHERE"
  Position: 44ERROR: syntax error at or near "WHERE"

Re: PostgreSQL output Update or insert error.

Anonymous — Tue, 12 Aug 2014 09:59:47 GMT

Hi,
I wonder how it is possible that all the columns are keys and you want an update??? or want an insert where there is nothing to insert because all are keys... can you show sample data?
- You can think of using tPostgresRow component to insert or update records based on query if it works in the browser...
vaibhav

Re: PostgreSQL output Update or insert error.

_AnonymousUser — Tue, 12 Aug 2014 10:29:55 GMT

The situation is pretty much the same as here:

Ah, no links allowed. Anyway you can find the topic
I was referring to with Vertica output update error
in Usage, operation.
JB

Re: PostgreSQL output Update or insert error.

_AnonymousUser — Tue, 12 Aug 2014 10:37:24 GMT

Hi,
I wonder how it is possible that all the columns are keys and you want an update??? or want an insert where there is nothing to insert because all are keys... can you show sample data?
- You can think of using tPostgresRow component to insert or update records based on query if it works in the browser...
vaibhav

Hi Vaibhav.
I'm testing Talend and extracting values from data that are not
in the lookup tables and inserting that data into another table
for later processing. Once there is a new value from the customer
seems that it comes in such a many numbers that I don't want
to record every instance of it but instead just one I tested if it
could be dealt with Insert or upgrade. Anyway, it proved so
slow (an buggy) that I'm open to new ideas. Just decided to
report on what seems like a bug to me.
JB

Re: PostgreSQL output Update or insert error.

veryimportantdude — Fri, 17 Oct 2014 08:53:53 GMT

I just found out the most stupid thing in Talend.
I am inserting/updating table with two columns (id, name). since id is serial on db site I am only inserting into name filed that is also key for update and insert. Now the job wont work so I had to add another column to the database where I insert some string (e.g. "whatever") and now IT WORKS!!!
How stupid can that be??
VErsions of Talend after 5.0 have too many bugs and defects. It is getting worse. sorry

Re: PostgreSQL output Update or insert error.

Anonymous — Fri, 17 Oct 2014 10:24:51 GMT

Hi Yebach,

I just found out the most stupid thing in Talend.
I am inserting/updating table with two columns (id, name). since id is serial on db site I am only inserting into name filed that is also key for update and insert. Now the job wont work so I had to add another column to the database where I insert some string (e.g. "whatever") and now IT WORKS!!!
How stupid can that be??
VErsions of Talend after 5.0 have too many bugs and defects. It is getting worse. sorry

Thanks for reporting this issue here.
Did your issue repro on other version? 5.5.1?

Best regards
Sabrina

Re: PostgreSQL output Update or insert error.

veryimportantdude — Fri, 24 Oct 2014 09:04:10 GMT

I have not tested on older version. but as far as i remember this was not the case