Thanks. Nice one. Would not have thought to try.
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""_"MM"_"DD');
to_date
2024-08-08
(1 row)
But as Greg said, your strings are perfectly sortable.
On Thu, A
On Fri, 9 Aug 2024 at 06:20, Ron Johnson 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 app
This helps. Thank you very much.
On Fri, 9 Aug 2024 at 02:15, Greg Sabino Mullane wrote:
> _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:
>
> SELEC
On Thu, Aug 8, 2024 at 4:46 PM Greg Sabino Mullane
wrote:
> _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
_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
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>_p_MM_DD".
We have a requirement of extracting specific partitions ordered by the date
criteria and also do some operations
