Question 114382 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to get range partition details from system catalogs

I am looking for a solution that lists all the range partition information. Tried the below query.

 SELECT c.relname as partition_list,p.relname as parent_tbl FROM pg_inherits i JOIN pg_class p ON i.inhparent = p.oid
JOIN pg_class c ON i.inhrelid = c.oid WHERE  p.relkind IN ('r', 'p');

output

"testpartpartition_1"    "parentpartiontbl"
"testpartpartition_2"    "parentpartiontbl"

But since I created a range partition, want to know the range values for eg:

CREATE TABLE testpartpartition_1 PARTITION OF parentpartiontbl FOR VALUES FROM (1) TO (5)
CREATE TABLE testpartpartition_2 PARTITION OF parentpartiontbl FOR VALUES FROM (6) TO (10)

Want the output also which states startvalue and endvalue for each partition like below

child_partition            parent_tbl           min_rangeval      max_rangeval
 ---------------------------------------------------------------------------------
 "testpartpartition_1"    "parentpartiontbl"         1                  5
 "testpartpartition_2"    "parentpartiontbl"         6                  10
Tags:

3

Answers


  1. 0

    Since the partition boundaries are stored in binary parsed form, all you can do is deparse them:

    SELECT c.oid::regclass AS child_partition,
       p.oid::regclass AS parent_tbl,
       pg_get_expr(c.relpartbound, c.oid) AS boundaries
FROM pg_class AS p
   JOIN pg_inherits AS i ON p.oid = i.inhparent
   JOIN pg_class AS c ON i.inhrelid = c.oid
WHERE p.relkind = 'p';

 child_partition │ parent_tbl │                                boundaries                                
═════════════════╪════════════╪══════════════════════════════════════════════════════════════════════════
 part_2022       │ part       │ FOR VALUES FROM ('2022-01-01 00:00:00+01') TO ('2023-01-01 00:00:00+01')
(1 row)

    Analyzing the boundary string is left as exercise to the reader.

    Login or Signup to reply.
  2. 0

    Here’s the query that gets generated by d+:

    edb=# SELECT c.oid::pg_catalog.regclass, c.relkind, false AS inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
        FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
        WHERE c.oid = i.inhrelid AND i.inhparent = '33245'
        ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
         oid         | relkind | inhdetachpending |         pg_get_expr         
---------------------+---------+------------------+-----------------------------
 testpartpartition_1 | r       | f                | FOR VALUES FROM (1) TO (5)
 testpartpartition_2 | r       | f                | FOR VALUES FROM (6) TO (10)
(2 rows)

    Looks like you need to use pg_get_expr() to decode the stuff in pg_class.relpartbound to reconstruct the range partition parameters.

    You can also replace i.inhparent = '33245' with a subquery to query by parent table name:

    edb=# SELECT c.oid::pg_catalog.regclass, c.relkind, false AS inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
        FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
        WHERE c.oid = i.inhrelid AND i.inhparent = (SELECT oid from pg_class where relname = 'parentpartitiontbl')
        ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
    Login or Signup to reply.
  3. 0

    You can find the information in the relpartbound column of the system catalog pg_class. Use the function pg_get_expr() to get the data readable:

    select 
    relname as partition_table, 
    pg_get_expr(relpartbound, oid) as partition_range
from pg_class
where relispartition
and relkind = 'r';

   partition_table   |       partition_range
---------------------+-----------------------------
 testpartpartition_1 | FOR VALUES FROM (1) TO (5)
 testpartpartition_2 | FOR VALUES FROM (6) TO (10)
(2 rows)

    Use regexp_matches() to extract the numbers in parentheses

    select 
    relname as partition_table,
    matches[1] as min_rangeval,
    matches[2] as max_rangeval
from pg_class
cross join regexp_matches(pg_get_expr(relpartbound, oid), '((.+?)).+((.+?))') as matches
where relispartition 
and relkind = 'r';

   partition_table   | min_rangeval | max_rangeval
---------------------+--------------+--------------
 testpartpartition_1 | 1            | 5
 testpartpartition_2 | 6            | 10
(2 rows)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search