CODEWITHSUNDEEP
postgresqldatetalend
DavidB
DavidB

Reputation: 66

Insert yyyyMMdd string into date column using Talend

I have the follow situation:

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

Upvotes: 0

Views: 1546

Answers (4)

Horse_1995
Horse_1995

Reputation: 247

Try like this,

enter image description here

The data in input file is: 20170101(in String format)

then set the tMap like, enter image description here

The output is as follows:

enter image description here

Upvotes: 0

DavidB
DavidB

Reputation: 66

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.

Upvotes: 0

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5940

[..] (209,2017-01-01 00:00:00.000000 +01:00:00,207,7.7,NULL,NULL,NULL,NULL,NULL)[..]

If Talend doesn't know by itself that passing timestamp into query requires it to be single quoted, then if possible - you need to do it.

Upvotes: 0

Evan Carroll
Evan Carroll

Reputation: 1

You don't have to do anything to insert a string like 20170101 into a date column. PostgreSQL will handle it for you it's just ISO 8601's date format.

CREATE TABLE foo ( x date );
INSERT INTO foo (x) VALUES ( '20170101' );

This is just a talend problem, if anything.

Upvotes: 3

Related Questions