Thread: Getting specific partition from the partition name
Hi ,
We are using postgres version 15.4. We have a range partition table and the partition naming convention is generated by pg_partman and is something like "table_name>_pYYYY_MM_DD".
We have a requirement of extracting specific partitions ordered by the date criteria and also do some operations on that specific date. But I am struggling and it's not working as expected.I tried something as below but it's not working.Can somebody guide me here please.
to_date( substring('table_part_p2024_08_08' from '_p(\d{4})_(\d{2})_(\d{2})'), 'YYYY_MM_DD'
) < current_date
or is there any ready-made data dictionary which will give us the order of the partitions by the date and we can get hold of the specific nth partition in that table?
Regards
Veem
YYYY_MM_DD is already setup for sorting, so just do:
SELECT table_name FROM information_schema.tables WHERE table_name ~ 'table_part_p' ORDER BY 1 DESC;
If you need to grab the numbers:
SELECT substring('table_part_p2022_03_04' from '([\d_]+)$');
Cheers,
Greg
On Thu, Aug 8, 2024 at 4:46 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:
YYYY_MM_DD is already setup for sorting, so just do:SELECT table_name FROM information_schema.tables WHERE table_name ~ 'table_part_p' ORDER BY 1 DESC;If you need to grab the numbers:SELECT substring('table_part_p2022_03_04' from '([\d_]+)$');
What if the partitions aren't all rationally named? There must be a pg_* table out there which contains the partition boundaries...
Death to America, and butter sauce.
Iraq lobster!
This helps. Thank you very much.
On Fri, 9 Aug 2024 at 02:15, Greg Sabino Mullane <htamfids@gmail.com> wrote:
YYYY_MM_DD is already setup for sorting, so just do:SELECT table_name FROM information_schema.tables WHERE table_name ~ 'table_part_p' ORDER BY 1 DESC;If you need to grab the numbers:SELECT substring('table_part_p2022_03_04' from '([\d_]+)$');Cheers,Greg
On Fri, 9 Aug 2024 at 06:20, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
What if the partitions aren't all rationally named? There must be a pg_* table out there which contains the partition boundaries...
The pg_class column relpartbound contains an internal representation of the partition boundary, when applicable.
You can decompile it into the canonical text format with pg_get_expr( expr pg_node_tree, relation oid [, pretty boolean ] ) → text.
So:
create table t(x int primary key) partition by list(x);
create table u partition of t for values in (0,1);
create table v partition of t for values in (2,3,4,5,6,7,8,9);
select oid::regclass,pg_get_expr(relpartbound,oid) from pg_class where relkind='r' and relispartition;
oid | pg_get_exprcreate table u partition of t for values in (0,1);
create table v partition of t for values in (2,3,4,5,6,7,8,9);
select oid::regclass,pg_get_expr(relpartbound,oid) from pg_class where relkind='r' and relispartition;
-----+----------------------------------------
u | FOR VALUES IN (0, 1)
v | FOR VALUES IN (2, 3, 4, 5, 6, 7, 8, 9)
(2 rows)
Best,
Giovanni
If you want to convert your table name into a timestamp, you don't need substring or similar. This also works:
to_date
------------
2024-08-08
(1 row)
But as Greg said, your strings are perfectly sortable.
On Thu, Aug 8, 2024 at 9:52 PM veem v <veema0000@gmail.com> wrote:
Hi ,We are using postgres version 15.4. We have a range partition table and the partition naming convention is generated by pg_partman and is something like "table_name>_pYYYY_MM_DD".We have a requirement of extracting specific partitions ordered by the date criteria and also do some operations on that specific date. But I am struggling and it's not working as expected.I tried something as below but it's not working.Can somebody guide me here please.to_date( substring('table_part_p2024_08_08' from '_p(\d{4})_(\d{2})_(\d{2})'), 'YYYY_MM_DD') < current_dateor is there any ready-made data dictionary which will give us the order of the partitions by the date and we can get hold of the specific nth partition in that table?RegardsVeem
Thanks. Nice one. Would not have thought to try.