Change the control column of a partitioned table

[pratik97]

I want to change the control column of my table from A to B. What is the best way to do that with partman?

[keithf4]

For native partitioning, I don't believe you can change the partition key of an existing set. At least I'm not seeing any looking at the documentation. The partition key is declared at the CREATE TABLE call and I don't see any options for changing that via ALTER TABLE.

So, unless someone else knows a better way, to change the control column you would have to migrate your data to a new partition set.

[pratik97]

@keithf4 my p_type is 'partman', is there any way to do it with non-native partitioning?

[keithf4]

This would be very challenging even on non-native. There is nothing in the child tables themselves that specifically tracks what the control column is. That is all managed by the trigger on the parent and the constraints on the children. So you'd have to update the part_config table to set the new column, rewrite the trigger for the new column, & update all child constraints for the new column. And that last part would also be difficult because the existing children have data that is based on a different column, so the constraint likely wouldn't work. So, in the end, you'd have to repartition all the data anyway to fully migrate to the new column.

I believe it would just be easier to move to a new partition set, just like you'd have to do for native.