Invalid input syntax for timestamp error with Postgres Copy command

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: "to_timestamp(1641278614000)"

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 1

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:

• COPY command: copy only specific columns from csv
• How to update selected rows with values from a CSV file in Postgres?