Search code examples
postgresqltalend

receiving batch error running an update on talend into PostgreSQL database

I have a talend solution where inside it rests a tMap --> tPostgreSQLOutput.

Inside the schema is a integer(key field) and a Date(Timestamp) in the format of "dd-MM-yyyy HH:mm:ss". The intent is to update the date field with the current time/date (Timestamp).

the date is set with this talend function call in the tMap:

TalendDate.parseDate("yyyy-MM-dd HH:mm:ss", TalendDate.getDate("yyyy-MM-dd HH:mm:ss"))

I have confirmed the date(timestamp) format, and confirmed that the timestamp data type in the PostgreSQL database. However, I'm getting this error upon runtime:

Batch entry 0 UPDATE "bitcoin_options" SET "last_notified" = 2017-04-08 12:02:40.000000 -05:00:00 WHERE "id" = 3 was aborted.  Call getNextException to see the cause.

I took the query it errored and manually ran it into PostgreSQL. I got this response:

ERROR:  syntax error at or near "11"
LINE 1: ...bitcoin_options" SET "last_notified" = 2017-04-08 11:53:11.0...
                                                             ^

Again, I checked the format, the datatype, and compared them against other tables and their UPSERTS. same format. same datatype.

In addition, I attempted to add a second space between date and time, with no avail.

UPDATE 1

I updated the tMap output to:

TalendDate.getCurrentDate();

and got the same error. Thanks

UPDATE 2

Here's my layout for Talend:

enter image description here

Solution

  • I figured it out. after much trial and error. The tPostgresSQLCommit x3 was redundant. When I removed the first two and placed just one, it gave me the proper output.

    LESSONS LEARNED: You only need 1 commit.