public inbox for [email protected]
help / color / mirror / Atom feedRe: Getting specific partition from the partition name
4+ messages / 4 participants
[nested] [flat]
* Re: Getting specific partition from the partition name
@ 2024-08-08 20:45 Greg Sabino Mullane <[email protected]>
2024-08-09 04:20 ` Re: Getting specific partition from the partition name Ron Johnson <[email protected]>
2024-08-09 11:23 ` Re: Getting specific partition from the partition name veem v <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Greg Sabino Mullane @ 2024-08-08 20:45 UTC (permalink / raw)
To: veem v <[email protected]>; +Cc: pgsql-general <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Getting specific partition from the partition name
2024-08-08 20:45 Re: Getting specific partition from the partition name Greg Sabino Mullane <[email protected]>
@ 2024-08-09 04:20 ` Ron Johnson <[email protected]>
2024-08-09 15:34 ` Re: Getting specific partition from the partition name GF <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Ron Johnson @ 2024-08-09 04:20 UTC (permalink / raw)
To: pgsql-general <[email protected]>
On Thu, Aug 8, 2024 at 4:46 PM Greg Sabino Mullane <[email protected]>
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!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Getting specific partition from the partition name
2024-08-08 20:45 Re: Getting specific partition from the partition name Greg Sabino Mullane <[email protected]>
2024-08-09 04:20 ` Re: Getting specific partition from the partition name Ron Johnson <[email protected]>
@ 2024-08-09 15:34 ` GF <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: GF @ 2024-08-09 15:34 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general <[email protected]>
On Fri, 9 Aug 2024 at 06:20, Ron Johnson <[email protected]> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Getting specific partition from the partition name
2024-08-08 20:45 Re: Getting specific partition from the partition name Greg Sabino Mullane <[email protected]>
@ 2024-08-09 11:23 ` veem v <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: veem v @ 2024-08-09 11:23 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: pgsql-general <[email protected]>
This helps. Thank you very much.
On Fri, 9 Aug 2024 at 02:15, Greg Sabino Mullane <[email protected]> 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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-08-09 15:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-08 20:45 Re: Getting specific partition from the partition name Greg Sabino Mullane <[email protected]>
2024-08-09 04:20 ` Ron Johnson <[email protected]>
2024-08-09 15:34 ` GF <[email protected]>
2024-08-09 11:23 ` veem v <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox