How to alter partition in postgresql

Question

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

I have never done this, but looking at the manual I would assume you need to detach the partition, then re-attach it with the new partition bounds — user330315
– user330315, Commented Oct 21, 2021 at 5:55

user330315 · Accepted Answer · 2021-10-21 06:28:13Z

15

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

answered Oct 21, 2021 at 6:28

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Ganesh Over a year ago

thats for the solution. Even this query worked for me. CREATE TABLE others PARTITION OF pqr FOR VALUES FROM ('280000000') TO (MAXVALUE);

Collectives™ on Stack Overflow

How to alter partition in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related