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');
"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
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)