Contents
pg_partition_tree()
A function for listing a partitioned relation's constituent partitions
pg_partition_tree()
is a system function for listing a partitioned table's or index's constituent partitions and their relationships.
pg_partition_tree()
was added in PostgreSQL 12.
Usage
pg_partition_tree (regclass
) →
setof record (relid
regclass
,parentrelid
regclass
,isleaf
boolean
,level
integer
)
No rows are returned if the specified relation is not a partitioned table or index.
Change history
- PostgreSQL 12
- added (commit d5eec4ee)
Examples
With the following sample partitioned table:
CREATE TABLE partition_test ( id INT NOT NULL PRIMARY KEY, val TEXT ) PARTITION BY RANGE (id); CREATE TABLE partition_test_default PARTITION OF partition_test DEFAULT; CREATE TABLE partition_test_1_10 ( id INT NOT NULL PRIMARY KEY CHECK (id BETWEEN 1 AND 10), val TEXT ) PARTITION BY RANGE (id); CREATE TABLE partition_test_11_20 ( id INT NOT NULL PRIMARY KEY CHECK (id BETWEEN 11 AND 20), val TEXT ); ALTER TABLE partition_test ATTACH PARTITION partition_test_1_10 FOR VALUES FROM (1) TO (11); ALTER TABLE partition_test ATTACH PARTITION partition_test_11_20 FOR VALUES FROM (11) TO (21); CREATE TABLE partition_test_1_5 ( id INT NOT NULL PRIMARY KEY, val TEXT, CONSTRAINT partition_test_1_10_id_check CHECK (id BETWEEN 1 AND 10) ); CREATE TABLE partition_test_6_10 ( id INT NOT NULL PRIMARY KEY, val TEXT, CONSTRAINT partition_test_1_10_id_check CHECK (id BETWEEN 1 AND 10) ); ALTER TABLE partition_test_1_10 ATTACH PARTITION partition_test_1_5 FOR VALUES FROM (1) TO (6); ALTER TABLE partition_test_1_10 ATTACH PARTITION partition_test_6_10 FOR VALUES FROM (6) TO (11);
pg_partition_tree()
produces following output:
postgres=# SELECT * FROM pg_partition_tree('partition_test'); relid | parentrelid | isleaf | level ------------------------+---------------------+--------+------- partition_test | | f | 0 partition_test_default | partition_test | t | 1 partition_test_1_10 | partition_test | f | 1 partition_test_11_20 | partition_test | t | 1 partition_test_1_5 | partition_test_1_10 | t | 2 partition_test_6_10 | partition_test_1_10 | t | 2 (6 rows)
Executing on a relation which is not part of a partitioning hierarchy:
postgres=# SELECT * FROM pg_partition_tree('pg_class'); relid | parentrelid | isleaf | level -------+-------------+--------+------- (0 rows)
Executing pg_partition_tree()
with the name of a non-existent relation:
postgres=# SELECT * FROM pg_partition_tree('foo'); ERROR: relation "foo" does not exist LINE 1: SELECT * FROM pg_partition_tree('foo');
References
- PostgreSQL documentation: Partitioning Information Functions