Importing datetime fields in "m/dd/yyyy" format using PostgreSQL COPY command

Question

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

• Peter Eisentraut: Works for me. Please produce a more complete example to reproduce, including tables and actual input data.
• aakash: Thanks. I just updated with a test case that does not work for me.

Answer 1

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.

• Mark: Using "crush tools" to physically alter files doesn't seem to get at what the author likely needs. (and what I was looking for when I stumbled on this.) It's not likely we are doing a one-time cleanup, and we don't want to change the source files especially when we want to preserve the data files for archival and disaster recovery when we get them from external customers. Rather, we want to give PostGres a hint on how to typecast on insert when timestamp fields we get aren't formatted in cardinality order. I think we are asking if we can hint in the table DDL to typecast on insert.