Invalid input syntax for timestamp error with Postgres Copy command

Posted on

Question :

I am trying to load data in Postgres through COPY command in a SQL file

COPY <table name>  (<column1>, <column2>, <column3> etc etc) FROM stdin;

1    test1   test2   to_timestamp(1592818988000) 

But I am getting this below error:

psql:/Users/sanupin/load.sql:1045: ERROR: invalid input syntax for
type timestamp: “to_timestamp(1641278614000)” CONTEXT: COPY
cstone_storage, line 1, column last_loaded:

Any idea what could be the problem? I know I have to convert the millisecond to second (div by 1000) on the epoch number, but not sure how else to proceed.

Answer :

COPY does not evaluate expressions. It takes data only. The manual:

COPY moves data between PostgreSQL tables and standard file-system files.

(With the exception of an optional WHERE clause added with Postgres 12.)

To evaluate expressions, use INSERT.

I suggest to COPY to a temporary staging table – with a double precision column for the yet unconverted timestamp – and INSERT from there. See:

Leave a Reply

Your email address will not be published. Required fields are marked *