0

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.

0

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.