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.
Advertisement
Answer
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: