pg_partition_ancestors()

A function listing a relation's ancestor partitions

pg_partition_ancestors() is a system function listing the ancestor partitions of the provided relation.

pg_partition_ancestors() was added in PostgreSQL 12.

Usage

pg_partition_ancestors ( regclass ) → setof regclass

Relations are listed in reverse hierarchical order, starting with the provided relation.

No rows are returned if the specified relation is not a partitioned table or index.

Change history

Examples

Assuming the partitioned table defined in the pg_partition_tree() example, pg_partition_ancestor() returns the following output:

postgres=# SELECT pg_partition_ancestors('partition_test_6_10');
 pg_partition_ancestors 
------------------------
 partition_test_6_10
 partition_test_1_10
 partition_test
(3 rows)

Executing on a relation which is not part of a partitioning hierarchy:

postgres=# SELECT pg_partition_ancestors('pg_class');
 pg_partition_ancestors 
------------------------
(0 rows)

Executing pg_partition_ancestors() with the name of a non-existent relation:

postgres=# SELECT pg_partition_ancestors('foo');
ERROR:  relation "foo" does not exist
LINE 1: SELECT pg_partition_ancestors('foo');

Categories

Partitioning, System function

See also

pg_partition_tree(), pg_partition_root()