PostgreSQL output Update or insert error.

One Star

PostgreSQL output Update or insert error.

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"

JB
Four Stars

Re: PostgreSQL output Update or insert error.

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

Re: PostgreSQL output Update or insert error.

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

Re: PostgreSQL output Update or insert error.

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

Re: PostgreSQL output Update or insert error.

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
Moderator

Re: PostgreSQL output Update or insert error.

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: PostgreSQL output Update or insert error.

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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now