How to alter partition in postgresql

user_16914599 0
Viewed 1633

Actually I am new to PostgreSQL. I want to alter existing partition to increase range value. For example I have below partition

PQR_271 FOR VALUES FROM ('260000000') TO ('270000000')

Here I want to extend the range to max value but I am not able to do the same.

I tried below solution

CREATE TABLE public.PQR_272 PARTITION OF public.stats_to_institution FOR VALUES FROM ('270000000') TO ('280000000');
ALTER TABLE public.PQR_272 OWNER to usr_replica;

Here I can increase the integer value but I cannot increase to maxvalue. Is there any solution to set range to maxvalue?

PostgreSQL version11

postgresql-11 database-partitioning postgresql
1 Answers

You can't alter the definition of a partition, but you can re-attach it with a different definition.

alter table stats_to_institution detach partition pqr_272;
alter table stats_to_institution attach partition pqr_272 
  for values FROM ('270000000') TO ('300000000'); --<< new upper bound

Obviously the data in that partition won't be visible in the main table between the detach and attach operation, but the data is still there.

Online example

user_3303150
Related