postgresqlpartitioningrange-partitions

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

Solution

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