Re: Getting specific partition from the partition name

Поиск
Список
Период
Сортировка
От Torsten Förtsch
Тема Re: Getting specific partition from the partition name
Дата
Msg-id CAKkG4_knN0ayoanYSzMuG5PJBkv7q+kBmBJCobxq9kFv24Z-YQ@mail.gmail.com
обсуждение исходный текст
Ответ на Getting specific partition from the partition name  (veem v <veema0000@gmail.com>)
Ответы Re: Getting specific partition from the partition name
Список pgsql-general
Дерево обсуждения
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:
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

В списке pgsql-general по дате отправления: