pg_partition_tree()

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

• PostgreSQL documentation: Partitioning Information Functions