Question 114608 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Which data type should i choose?

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search