Which data type should i choose?

Denzel 0
Viewed 39

I want to export csv file to postgresql db using pentago data integration CE. Which data type should i choose both in pentaho and in postgresql to export data correctly? I am using Pentaho CSV file input. Especially I have problems with the date. My CSV looks like this:

date,x,y,mm,option
2017-01-01 00:00,101,1,0.004,0
2017-01-01 00:00,101,3,0.059000000000000004,0
2017-01-01 00:00,101,5,1.2,0
2017-01-01 00:00,101,6,0.002,0
pentaho postgresql
2 Answers

What is the date problem? As I don't see it:

select '2017-01-01 00:00'::date 01/01/2017.

CSV is a text format so data type really does not come into play there. Postgres will automatically cast most reasonable text representations to the appropriate type. So 0.059000000000000004 to numeric like:

select '0.059000000000000004'::numeric; 0.059000000000000004.

Assuming the values are consistent per your example and you really want date not timestamp then the Postgres types should be:

date, integer, integer, numeric, integer

Adrian Klaver0

When reading a file, PDI guess the data type of the columns reading the first rows, and sometimes the sample taken isn't enough for PDI to make correct guesses, so in the read file step you have to check and help providing the correct format if PDI hasn't guessed correctly, date values are always a headache for that.

Besides, keep in mind that Date and Timestamp aren't equivalent, sometimes I need adding a Set values step, and using the Metadata tab in that step to convert between Date and Timestamp and viceversa, so keep that step in mind when you are having trouble with dates.

Ana GH0
Related