1

I'm looking to import a series of CSV files that contain a datetime field in the format of m/dd/yyyy h:mm:ss into a PostgreSQL table using the native /copy command.

I get the following error on import:

ERROR: date/time field value out of range: "5/13/2010 0:00:00"
HINT: Perhaps you need a different "datestyle" setting.

I tried setting the datestyle setting to ISO,MDY and SQL,MDY to no avail.I also attempted to modify the field type in the target table to character to force the fields to be read as strings, but that didn't work either.

Any ideas? Ideally I would like to have the data directly read into the target table as dates, but would settle for character strings. Thanks in advance!

Test Case

PostgreSQL Target Table:

CREATE TABLE testtable
(
    count_date timestamp without time zone,
    location character(40),
    count smallint
)
WITH (
    OIDS=TRUE
);

Data:

"count_date","location","count"
5/12/2010 0:00:00,"SEGMENT A",50
5/12/2010 0:00:00,"SEGMENT B",40
5/12/2010 0:00:00,"SEGMENT C",30
5/13/2010 0:00:00,"SEGMENT A",20
5/13/2010 0:00:00,"SEGMENT B",10
5/13/2010 0:00:00,"SEGMENT C",5
2
  • Works for me. Please produce a more complete example to reproduce, including tables and actual input data. – Peter Eisentraut Aug 23 '16 at 18:33
  • Thanks. I just updated with a test case that does not work for me. – aakash Aug 23 '16 at 20:06
0

I'd recommend always using ISO date format for any kind of data interchange.

Consider Google's Crush Tools convdate for a quick fix:

  convdate --delim ',' --field 1 --preserve_header --input_format '%m/%d/%Y %I:%M:%S' --output_format '%Y-%m-%dT%H:%M:%S' < myData.csv |
  psql -c '\copy foo from stdin with (format csv, header true)'

0:00:00 is probably not a valid time though. Use csvquote if your data has embedded commas or newlines.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.