Transfer Microsecond Timestamp into PostgreSQL Table Using COPY

copypostgresqltimestamp

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?

Best Answer

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 

INSERT INTO 
     dest(col1,                     col2) 
SELECT    
          f1(orig_col1, orig_col2), f2(orig_col1, orig_col2) 
FROM 
     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).

Related Question