In that example we are doing the followint steps:
Find which partition contains specific rows in partition table stock.request_pt0_arch
Retrieve partition bounderies from partition table stock.request_pt0_arch
Detach partition from table stock.request_pt0_arch
attach Partition in Table stock.request_pt0
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');