I need to transfer a microsecond timestamp into a table by using the COPY command.

My approach using a normal INSERT, where X is a timestamp (since 1.1.1970) in microseconds, looked like this:

TO_TIMESTAMP( X ::double precision / 100000)

So, an X of 1484579365136360 would translate to 1/16/2017, 4:09:25.136360 PM.

How could I do the same with the COPY command?

I expect I need to convert the timestamp before issuing the COPY command, but in what format?

  • Are you copying from a table to a file or from a file to a table? If it's the latter, it doesn't seem like there's an option to introduce any data transformation during the copying. I'm not an expert, though, just reading the docs and can't see how it can be done. – Andriy M Jan 16 '17 at 15:53
  • I'm copying from STDIN to the table with libpq. Do you know which format I could use to express a microsecond precise time which postgres is able to understand? – jonadev95 Jan 16 '17 at 16:00
  • 1
    the easiest would be copy data as is and then add column, update it with TO_TIMESTAMP( X ::double precision / 100000), drop original column and rename new to old – Vao Tsun Jan 16 '17 at 16:19

What you're trying to do is normally referred as an ETL (Extract, Transform, Load) process.

You want to do the transformation within the database. This is normally possible by using an intermediate table, where you store (as text, most probably) the data from STDIN via libpq and COPY.

COPY intermediate_table (orig_col1, orig_col2)
FROM stdin 
WITH FORMAT CSV  ;  -- Choose your parameters

Then transfer it to the second table by means of

     dest(col1,                     col2) 
          f1(orig_col1, orig_col2), f2(orig_col1, orig_col2) 
     intermediate_table ;

That is, you have some transforming functions on the original data to produce the data you want to store. For instance f1 could just be your TO_TIMESTAMP(orig_col_1 ::double precision / 100000).

After transforming the data from the intermediate table(s), it is common to truncate them (or drop them, and recreate them next time).

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.