Search code examples
postgresqldatetalend

Insert yyyyMMdd string into date column using Talend

I have the follow situation:

  • A PostgreSQL database with a table that contains a date type column called date.

  • A string from a delimited .txt file outputting: 20170101.

I want to insert the string into the date type column.

So far i have tried the following with mixed results/errors:

row1.YYYYMMDD 
Detail Message: Type mismatch: cannot convert from String to Date

Explanation: This one is fairly obvious.

TalendDate.parseDate("yyyyMMdd",row1.YYYYMMDD) 
Batch entry 0 INSERT INTO "data" ("location_id","date","avg_winddirection","avg_windspeed","avg_temperature","min_temperature","max_temperature","total_hours_sun","avg_precipitation") VALUES (209,2017-01-01 00:00:00.000000 +01:00:00,207,7.7,NULL,NULL,NULL,NULL,NULL) was aborted.  Call getNextException to see the cause.

can see the string parsed into "2017-01-01 00:00:00.000000 +01:00:00".

When I try to execute the query directly i get a "SQL Error: 42601: ERROR: Syntax error at "00" position 194"

Other observations/attempts:

The funny thing is if I use '20170101' as a string in the query it works, see below.

INSERT INTO "data" ("location_id","date","avg_winddirection","avg_windspeed","avg_temperature","min_temperature","max_temperature","total_hours_sun","avg_precipitation") VALUES (209,'20170101',207,7.7,NULL,NULL,NULL,NULL,NULL)

I've also tried to change the schema of the database date column to string. It produces the following:

Batch entry 0 INSERT INTO "data" ("location_id","date","avg_winddirection","avg_windspeed","avg_temperature","min_temperature","max_temperature","total_hours_sun","avg_precipitation") VALUES (209,20170101,207,7.7,NULL,NULL,NULL,NULL,NULL) was aborted.  Call getNextException to see the cause.

This query also doesn't work directly because the date isn't between single quotes.

What am i missing or not doing? (I've started learning to use Talend 2-3 days ago)

EDIT// Screenshots of my Job and tMap

https://i.sstatic.net/zbaQY.jpg

EDIT//It doesnt appear to be a date formatting problem but a Talend to PostgreSQL connection problem

EDIT// FIXED: It was a stupid easy problem/solution ofcourse. THe database name and schema name fields were empty... so it basically didnt know where to connect

Solution

  • FIXED: It was a stupid easy problem/solution ofcourse. THe database name and schema name fields were empty... so it basically didnt know where to connect thats why i got the BATCH 0 error and when i went deeper while debugging i found it couldnt find the table, stating the relation didnt exist.