Postgres COPY FROM Fails With invalid input syntax for type timestamp

Question

I have a pipe delimited file that I'm importing into Postgres (9.2.8) using the command:

COPY schema.tablename FROM '/path/to/file.csv' DELIMITERS '|' CSV

It has been working reliably for a while, but just choked on:

Query failed: ERROR: invalid input syntax for type timestamp: "Sep 24 2013 12:00:00:000AM"

That looks like a valid timestamp to me. Any suggestions?

Answer 1

It's the last :000 that's messing you up. For whatever reason, the timestamp-formatting gods decreed that milleseconds be delimited with a "." instead of a ":".

jberkus=# select timestamp 'Sep 24 2013 12:00:00:000AM';

ERROR:  invalid input syntax for type timestamp: "Sep 24 2013 12:00:00:000AM"

jberkus=# select timestamp 'Sep 24 2013 12:00:00.000AM';

          timestamp      
    ---------------------
    2013-09-24 00:00:00
    (1 row)