Clever Geek Handbook

Getting batch error when running update for talend in PostgreSQL database

I have a solution to a talent that lies within it tMap --> tPostgreSQLOutput

.

Inside the schema there is a integer

(key field) and a date (timestamp) in the format "dd-MM-yyyy HH:mm:ss"

. The goal is to update the date field with the current time / date (timestamp).

the date is set with this call to the talent function in tMap:

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

I have confirmed the date format (timestamp) and have confirmed that the datatype is timestamp in PostgreSQL database. However, I am getting this error at 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 error request and manually ran it in PostgreSQL. I got the answer:

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, datatype and compared them with other tables and their UPSERTS. in the same format. the same data type.

Also, I tried adding a second space between date and time, to no avail.

UPDATE 1

I updated the output tMap

to:

TalendDate.getCurrentDate();

and got the same error. Thanks to

UPDATE 2

Here is my layout for Talend:

enter image description here

+3


source to share


2 answers


I understood that. after much trial and error. The tPostgresSQLCommit x3 value was redundant. When I removed the first two and only posted one, it gave me the correct result.



SELECTED LESSONS: You only need 1 commit.

+4


source


Please note that the timestamp is not formatted correctly: UPDATE "bitcoin_options" SET "last_notified" = '2017-04-08 12:02:40.000000 -05:00:00' WHERE "id" = 3



The single quotes associated with the timestamp are missing. If you add those, you must be good to go.

+2


source






More articles:


All Articles