Converting dates between SQL Server and Postgres with Talend

Question

I have a Talend job that runs a query on a SQL Server database and then writes selected data into a Postgres database. I am using the tMap component to do the mapping. The SQL Server dates are in similar to 2014-01-20 11:34:26.0 (configured as '"yyyy-MM-dd HH:mm:ss.SSS" in Talend) and the Postgres dates yyyy-MM-dd.

When I run the job I get the following exception:

[statistics] connected
Batch entry 0 INSERT INTO "criticalcareepisodes" ("GivenName","FamilyName","height","weight","mrn","id","admissiondate") VALUES ("Adam","Apple",100,200.0,123456,nextval('CriticalCareEpisodes_id_seq'),2009-11-10 21:30:00.000000 +11:00:00) was aborted. Call getNextException to see the cause.
[statistics] disconnected
Job ExtractSLICCriticalCareEpisode ended at 14:06 27/09/2014. [exit code=0]

It seems that for some reason, the date is not being formatted as I would have expected. The exception is fairly obviously related to trying to push a date/time into a Postgres Date type - at least I think so. I am sure I am missing something obvious here, but any assistance would be appreciated.

I have added some images that may help:

Answer 1

I'm not familiar with Talend's software, but, from a Postgres perspective, it looks like the INSERT is trying to insert a timestamp using the non-string literal 2009-11-10 21:30:00.000000 +11:00:00, which isn't a valid thing to do in Postgres. timestamp literals need to be specified in string form (among other methods, but it can't be a non-string form like what is indicated in the log output).

One can do something similar in a string form, i.e. '2009-11-10 21:30:00.000000 +11:00'.

Why the Talend software is sending a non-string form timestamp like that may be a bug in that software, although it's not clear from the available information.

Here is an sqlfiddle which shows what I mean in more detail.

Note that this assumes that the log would properly delimit strings as they should be in the INSERT query, which isn't totally clear from the log (i.e. are Adam and Apple strings or not? As indicated, they aren't, but that's odd.

If they actually are strings, then the next step is to get exactly what the exception is, which apparently you need to call getNextException to determine.

Answer 2

Ken Hampson's answer covers the Postgres part of the problem: you're trying to insert a timestamp into a date field.

The problem is that you're trying to specify the intended format (yyyy-MM-dd) in the date pattern part of the Date type configuration in the schema. Talend actually holds the full date object at all times and the date pattern is merely for formatting purposes with Talend components such as a tLogRow that will then print the date in the format chosen.

To properly convert the date to a formatted string so you can send it to Postgres (which will implicitly convert the string into a date) you need to replace row1.CCDateAdmit with TalendDate.formatDate("yyyy-MM-dd",row1.CCDateAdmit).