I have a large, partitioned table with a field in that needs to upgraded from INT to BIGINT.
My intention is to detach each partition, make the change and then re-attach, using something like this:
ALTER TABLE testTable DETACH PARTITION testTable_201908; -- Should be very quick ALTER TABLE testTable_201908 ALTER COLUMN recordId TYPE bigint; -- likely to take ages! ALTER TABLE testTable_201908 ADD CONSTRAINT con_201908 CHECK ( loadedTime >= DATE '2019-08-01' AND loadedTime < DATE '2019-08-01' ); -- Likely to take ages. ALTER TABLE testTable ATTACH PARTITION testTable_201908 FOR VALUES FROM ('2019-08-01') TO ('2008-09-01' ); -- Should be quick with the constraint in place. ALTER TABLE testTable_201908 DROP CONSTRAINT con_201908; -- Should be quick
The table is really big (about 100 millions records per partition), dozens of partitions. I've done some testing,
DETACH PARTITION is really quick,
ALTER COLUMN recordId TYPE bigint takes ages (but I don't think there's anything I can do about this)
Then I have a choice, if I just re-attach the partition to the parent table (without a constraint) then that takes ages whilst the database checks integrity (at also seems to lock-up the parent table for the duration). OR, I can add a constraint (takes hours) and then re-attach to the parent (takes seconds only)
Is there a way I can force the re-attachment of the partition without checking integrity and without requiring a constraint? After-all, I know the partition was valid before I detached it, and I know I haven't altered any of the relevant data?