0

I have a PostgreSQL table with a column of type timestamp. I had included this column a while ago just in case I wanted to use it for something in the future. I am now looking to convert it into an int8 and use it as an epoch time column. All rows of the table have this column set to null at the moment. When I try to alter the row using:

ALTER TABLE public.new_ambient_data
ALTER COLUMN sensor_date TYPE int8 USING sensor_date::int8;

I get the error:

ERROR: cannot cast type timestamp without time zone to bigint

Is it better to just drop the column and make a new one of the data type that I want, or is here a better SQL script to convert the empty timestamp column to an int8.

Note: The table in question has over a million rows in it.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
clixxclixx
  • 147
  • 1
  • 1
  • 7

2 Answers2

3

First of all, the objective is undefined without clearing up what that int8 is going to represent. Seconds since the epoch? Milliseconds? Microseconds? (Won't matter in your particular case with all NULL values, but the next reader might be misguided.)

Next, in Postgres there is no cast defined for timestamp --> bigint (basically for the same reason). You need a valid expression for the USING clause.

Assuming you want microseconds because that's preserving the original microsecond resolution of Postgres timestamps, this will do the job:

ALTER TABLE public.new_ambient_data
   ALTER COLUMN sensor_date TYPE int8 USING (extract(epoch FROM sensor_date)*1000000)::int8;

Notably, the Postgres epoch for timestamps starts at 2000-01-01 00:00:00 UTC, unlike the UNIX epoch starting at 1970-01-01 00:00:00 UTC. But extract() returns the UNIX epoch (which can be converted back to timestamptz with to_timestamp()). So just converting the internal value wouldn't do.

For your particular case (all values NULL), it's simpler to use text as stepping stone. Every type can be cast from and to text (as long as the value is compatible).

ALTER TABLE public.new_ambient_data
   ALTER COLUMN sensor_date TYPE int8 USING sensor_date::text::int8;

And yes, it's probably cheaper to convert the column in place, than to drop and recreate it. While the column is all NULL, the operation is very cheap either way, as there is no actual tuple data, only a bit in the NULL bitmap. Neither way will trigger a table rewrite.

A newly added column always goes to the end of the columns list, while the converted one stays in place. Depends on what you want.

Finally, don't do it at all. The data type timestamp (or timestamptz) is typically superior to storing temporal information as generic bigint in multiple ways. See details in Laurenz' answer!

See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

First, I want to dissuade you from doing that. It is much better to use a timestamp column than to use a numeric column:

  • the values can more easily be understood by a human

  • you can make use of date arithmetic, so you don't lose anything:

    timestamp - timestamp → interval

    timestamp + interval → timestamp

    interval / double precision → interval

    This makes your queries more readable

  • if you use timestamp with time zone, you can have PostgreSQL handle time zone conversions for you

  • useful functions like date_part and date_trunc (and date_bin from v14 on!) to truncate the values and extract individual components

Internally, a timestamp is stored as a number anyway, so you don't lose performance.

The actual conversion is given in Erwin's answer, so I won't repeat that here.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263