3

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

user3210680
  • 98
  • 1
  • 1
  • 7

2 Answers2

5

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)
  );
AdamKG
  • 13,678
  • 3
  • 38
  • 46
  • This did the trick! updating the Q with my final query Only change between yours and mine is our bounds are always set to '[)' and for us these dates are 100% required so the script is run without CASE WHEN Though I imagine someone browsing this would make good use of that – user3210680 Feb 28 '20 at 16:02
2

Using the line from AdamKG's answer (selected as the BEST!) we arrive at this

-- -- Create a placeholder tstzrange column
ALTER TABLE SB ADD COLUMN run_dates_holder tstzrange NULL;

-- -- Update placeholder tstzrange column with existing run_dates
UPDATE SB SET run_dates_holder=tstzrange(lower(run_dates), upper(run_dates),'[)');

-- -- Remove original run_dates
ALTER TABLE SB DROP COLUMN run_dates;

-- -- Rename 'run_dates_holder' to 'run_dates'
ALTER TABLE SB RENAME COLUMN run_dates_holder to run_dates;

Test data's remained intact

user3210680
  • 98
  • 1
  • 1
  • 7