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?

FuzzyChef · Accepted Answer · 2014-05-02 22:30:38Z

9

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)

edited May 2, 2014 at 22:30

user330315

answered May 2, 2014 at 21:45

FuzzyChef

4,0712 gold badges19 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1517922 Over a year ago

Thank you! I can't change the 3rd party data, but I can at least scrub / fix it beforehand so it doesn't choke.

Collectives™ on Stack Overflow

Postgres COPY FROM Fails With invalid input syntax for type timestamp

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related