Datetime field overflow 7 error date time field value out of range

Seconds since epoch timestamps

Your «timestamp» is in seconds-since-epoch. Per dezso, use to_timestamp(). I missed that when I checked dfS+

Bad complex idea, and dragons.

Check the input formats. They cover casts from strings. That’s what COPY is doing under the hood. The only method that even remotely looks like a long number is ISO 8601. If you look at that example though you’ll see it’s not a seconds-since-epoch

Example    | Description
19990108   | ISO 8601; January 8, 1999 in any mode

This is basically the same as another example on that chart.

Example    | Description
1999-01-08 | ISO 8601; January 8, 1999 in any mode

Converting to timestamp with abstime as an intermediary format

So if you want to convert from seconds-since-epoch, you can cheat by using the internal abstime since there is no available cast directly to timestamp from a string of seconds-since-epoch.

SELECT 1421088300::abstime::timestamp;
      timestamp      
---------------------
 2015-01-12 12:45:00
(1 row)

What’s happening here is that abstime is binary coercable with integer. You can see that in dC+. I checked dfS+ for functions to get from integer to timestamp and found none. There is a cast though from integer to abstime (which is stored as an integer), and from abstime to timestamp.

If this is a new table you could actually type that column as abstime. It should load perfectly fine. And then you can ALTER TABLE. here is an example, except I’m not running COPY (but it should work all the same).

CREATE TABLE foo(bar)
AS VALUES
  (1421088300::abstime);

TABLE foo;
          bar           
------------------------
 2015-01-12 12:45:00-06
(1 row)

ALTER TABLE foo
  ALTER bar
  TYPE timestamp;

TABLE foo;
         bar         
---------------------
 2015-01-12 12:45:00
(1 row)

d foo;
                Table "public.foo"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 bar    | timestamp without time zone |