Thread

1. Getting specific partition from the partition name

   veem v <veema0000@gmail.com> — 2024-08-08T19:52:36Z

   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
   

2. Re: Getting specific partition from the partition name

   Greg Sabino Mullane <htamfids@gmail.com> — 2024-08-08T20:45:18Z

   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
   

3. Re: Getting specific partition from the partition name

   Ron Johnson <ronljohnsonjr@gmail.com> — 2024-08-09T04:20:04Z

   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!
   

4. Re: Getting specific partition from the partition name

   veem v <veema0000@gmail.com> — 2024-08-09T11:23:09Z

   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
   >
   >
   

5. Re: Getting specific partition from the partition name

   GF <phabriz@gmail.com> — 2024-08-09T15:34:54Z

   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
   

6. Re: Getting specific partition from the partition name

   Torsten Förtsch <tfoertsch123@gmail.com> — 2024-08-09T17:35:12Z

   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
   >
   

7. Re: Getting specific partition from the partition name

   Thiemo Kellner <thiemo@gelassene-pferde.biz> — 2024-08-10T05:42:46Z

   Thanks. Nice one. Would not have thought to try.