4

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

Community
  • 1
  • 1
arcee123
  • 101
  • 9
  • 41
  • 118

2 Answers2

4

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.

arcee123
  • 101
  • 9
  • 41
  • 118
2

Notice your timestamp is not properly formatted: UPDATE "bitcoin_options" SET "last_notified" = '2017-04-08 12:02:40.000000 -05:00:00' WHERE "id" = 3

It's missing the single quotes surrounding the timestamp. If you add those you should be good to go.

polmiro
  • 1,966
  • 15
  • 22
  • sorry....no dice. still gives me an error: `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.` – arcee123 Apr 11 '17 at 00:02