0

PostgreSQL's COPY command is useful to quickly import lots of data, and the data must be in the textual representation for the datatype.

I am importing lots of data, includes a timestamp column, but it's stored as "unix time", i.e. seconds since the epoch. I can convert it to ISO 8601 (e.g. 2010-01-01 00:00:00, and PostgreSQL accept that for a timestamp. It does not accept the raw epoch value integer.

Is it possible to get postgres to accept the epoch integer value and interpret/convert that to a timestamp? That would make my code simpiler (and maybe faster).

This works with psql:

create temporary table test1 ( v1 timestamp );
copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF     signal.
>> 2010-01-01 00:00:00
>> \.
COPY 1

But these don't:

copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 946684800
>> \.
ERROR:  date/time field value out of range: "946684800"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY test1, line 1, column v1: "946684800"
copy test1 from stdin ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> epoch + 946684800
>> \.
ERROR:  time zone displacement out of range: "epoch + 946684800"
CONTEXT:  COPY test1, line 1, column v1: "epoch + 946684800"

The to_timestamp function is able to accept an 'integer' or the string text of that integer. I can't see an option like this for DateStyle setting. I have tried with the column being a timestamp with time zone type, with the same results.

I'm currently runing PostgreSQL 10 on Ubuntu Linux, but upgrading to 11 (or 12 soon) is possible if needed.

1

There is no way to do that with COPY alone. You either have to pre-process or post-process the data.

Perhaps UNLOGGED tables can make this a little bit faster:

/* no WAL logging for better performance */
CREATE UNLOGGED TABLE test1 (v1 double precision);

COPY test1 FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> 946684800
>> \.

/* this will rewrite the table */
ALTER TABLE test1
   ALTER v1 TYPE timestamp without time zone
      USING (to_timestamp(v1) AT TIME ZONE 'UTC');

/* this will dump the table to the transaction log */
ALTER TABLE test1 SET LOGGED;

This will allow you to keep the intermediate manipulations out of the transaction log, which is good for performance.

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.