PostgreSQL alter column type from DATERANGE to TSTZRANGE and migrate the data

We’re changing a column run_dates (per request) from daterange to tstzrange, a column with a few months of test data. Essentially we want to migrate!

I’m running the script below, inspired by postgreSQL alter column data type to timestamp without time zone and I have to admit, my SQL background is basically nothing

-- Create a temporary TIMESTAMP column
ALTER TABLE table_name ADD COLUMN run_dates_holder TSTZRANGE NULL;

-- Copy casted value over to the temporary column
UPDATE table_name SET run_dates_holder = run_dates::TSTZRANGE;

-- Modify original column using the temporary column
ALTER TABLE table_name ALTER COLUMN run_dates TYPE
TSTZRANGE USING run_dates_holder;

-- Drop the temporary column (after examining altered column values)
ALTER TABLE table_name DROP COLUMN run_dates_holder;

Unfortunately….. These types don’t naturally translate.

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cannot cast type daterange to tstzrange 

and

psycopg2.ProgrammingError: cannot cast type daterange to tstzrange
LINE 5: ...ble_name SET run_dates_holder = run_dates::TSTZRANG...

Has anyone ever successfully migrated a daterange to a tstzrange?

As a backup, we can def just drop the daterange column and recreate as tstzrange, since that would only affect test data. Just less optimal for the team. It’s worth a shot to look into this, and I think the resolution here is at least worthwhile to future ‘daterange-to-tstzrange’ migrators, as I found no other docs/resources on the matter

Answer

You can’t just cast a daterange to a tstzrange. Use lower() and upper() to extract the bounds of the daterange and upper_inc() and lower_inc() to extract their inclusivity, and construct a new tstzrange.

UPDATE table_name
SET run_dates_holder=tstzrange(
  lower(run_dates), upper(run_dates),
  concat(
    CASE WHEN lower_inc(run_dates) THEN '[' else '(' END,
    CASE WHEN upper_inc(run_dates) THEN ']' ELSE ')' END)
  );