How to Detach and Attach Partition in PostgreSQL

In that example we are doing the followint steps:


1. Find Which partition contains specific rows in my PostgreSQL database 

SELECT tableoid::pg_catalog.regclass, id,requesttime

FROM stock.request_pt0_arch   

where reqid in(18844,18809,18803,18789,18785,18780,18777,18774,18770,18723,18718,18716,18714,18709,18799,18788,18779);

tableoid                                |id                |requesttime                      |

-------------------------------------+----------------+------------------------------------+

request_pt0_p275000000 275502238 2023-02-27 06:50:03.000

request_pt0_p275000000 275593692 2023-02-27 07:41:57.000

request_pt0_p275000000 275600912 2023-02-27 07:46:11.000

request_pt0_p275000000 275622054 2023-02-27 07:58:23.000

request_pt0_p275000000 275634260 2023-02-27 08:06:07.000

request_pt0_p275000000 275943272 2023-02-27 11:17:51.000

request_pt0_p275000000 275960450 2023-02-27 11:27:58.000

request_pt0_p275000000 275985060 2023-02-27 11:42:36.000

request_pt0_p275000000 275991404 2023-02-27 11:47:23.000

request_pt0_p275000000 275993172 2023-02-27 11:47:36.000

request_pt0_p275000000 275007162 2023-02-27 11:56:25.000

request_pt0_p275000000 276115536 2023-02-27 13:02:02.000

request_pt0_p275000000 276115820 2023-02-27 13:02:22.000

request_pt0_p275000000 276142062 2023-02-27 13:16:55.000

request_pt0_p275000000 276156628 2023-02-27 13:24:53.000

request_pt0_p275000000 2762110452 2023-02-27 13:54:39.000

request_pt0_p275000000 276331982 2023-02-27 15:01:43.000


2. Retrieve partitioning boundaries

SELECT t.oid::regclass AS partition,

       pg_get_expr(t.relpartbound, t.oid) AS bounds

FROM pg_inherits AS i

   JOIN pg_class AS t ON t.oid = i.inhrelid

WHERE i.inhparent = 'stock.request_pt0_arch'::regclass

order by 1;


partition                             |bounds                                                                           |

------------------------------------+-------------------------------------------------------------------------+

request_pt0p520000000|FOR VALUES FROM ('520000000') TO ('530000000')|

request_pt0p155000000|FOR VALUES FROM ('155000000') TO ('165000000')|

request_pt0p165000000|FOR VALUES FROM ('165000000') TO ('175000000')|

request_pt0p175000000|FOR VALUES FROM ('175000000') TO ('185000000')|

request_pt0p185000000|FOR VALUES FROM ('185000000') TO ('195000000')|

request_pt0p195000000|FOR VALUES FROM ('195000000') TO ('205000000')|

request_pt0p205000000|FOR VALUES FROM ('205000000') TO ('215000000')|

request_pt0p215000000|FOR VALUES FROM ('215000000') TO ('225000000')|

request_pt0p225000000|FOR VALUES FROM ('225000000') TO ('235000000')|

request_pt0p235000000|FOR VALUES FROM ('235000000') TO ('245000000')|

request_pt0p245000000|FOR VALUES FROM ('245000000') TO ('255000000')|

request_pt0p105000000|FOR VALUES FROM ('105000000') TO ('115000000')|

request_pt0p100000000|FOR VALUES FROM ('100000000') TO ('100500000')|

request_pt0p100500000|FOR VALUES FROM ('100500000') TO ('101000000')|

request_pt0p101000000|FOR VALUES FROM ('101000000') TO ('101500000')|

request_pt0p101500000|FOR VALUES FROM ('101500000') TO ('102000000')|

request_pt0p102000000|FOR VALUES FROM ('102000000') TO ('102500000')|

request_pt0p102500000|FOR VALUES FROM ('102500000') TO ('103000000')|

request_pt0p103000000|FOR VALUES FROM ('103000000') TO ('103500000')|

request_pt0p103500000|FOR VALUES FROM ('103500000') TO ('104000000')|

request_pt0p104000000|FOR VALUES FROM ('104000000') TO ('104500000')|

request_pt0p104500000|FOR VALUES FROM ('104500000') TO ('105000000')|

request_pt0p115000000|FOR VALUES FROM ('115000000') TO ('125000000')|

request_pt0p125000000|FOR VALUES FROM ('125000000') TO ('135000000')|

request_pt0p135000000|FOR VALUES FROM ('135000000') TO ('145000000')|

request_pt0p145000000|FOR VALUES FROM ('145000000') TO ('155000000')|

request_pt0p255000000|FOR VALUES FROM ('255000000') TO ('265000000')|

request_pt0p265000000|FOR VALUES FROM ('265000000') TO ('275000000')|

request_pt0p285000000|FOR VALUES FROM ('285000000') TO ('295000000')|

request_pt0p295000000|FOR VALUES FROM ('295000000') TO ('305000000')|

request_pt0p500000000|FOR VALUES FROM ('500000000') TO ('510000000')|

request_pt0p510000000|FOR VALUES FROM ('510000000') TO ('520000000')|

3. Detach Partition

alter table stock.request_pt0_arch detach partition stock.request_pt0_p275000000;

4. Attach Partition

alter table stock.request_pt0 attach partition stock.request_pt0_p275000000 FOR VALUES FROM ('275000000') TO ('280000000');