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:
Show quoted text
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_expr
-----+----------------------------------------
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:
=# select to_date('table_part_p2024_08_08', '"table_part_p"YYYY"_"MM"_"DD');
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:
Show quoted text
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?Regards
Veem
