View pg_partitioned_tables_infos
Creates a view to get information about partitioned tables. Since PostgreSQL 10 supports partitions, but they became usable only in later versions. A system table to identify partitions has been added in PostgreSQL 11. Therefore this view is only available on systems with PostgreSQL 11 or newer.
Columns
| Column Name | datatype | Description |
|---|---|---|
| parent_relid | oid | The oid of the parent table. |
| parent_schemaname | name | The schema where the parent table is located. |
| parent_tablename | name | The name of the parent table. |
| parent_owner | name | The role name of the owner of the parent table. |
| partition_strategy | text | The partition strategy of the partitioned table, this can be LIST, RANGE, or HASH. |
| count_of_partitions | bigint | The number of partitions of the parent table. |
| overall_size | numeric | The overall size of the table including all partitions. |
| child_relid | oid | The oid of the partition of a partitioned table. |
| child_schemaname | name | The name of the schema where the partition of a partitioned table is located. |
| child_tablename | name | The name of the partition of a partitioned table. |
| child_owner | name | The role name of the owner of the partition of a partitioned table. |
| child_size | bigint | The size of this partition. |
Example
SELECT *
FROM pg_partitioned_tables_infos;
Result:
| parent_relid | parent_schemaname | parent_tablename | parent_owner | partition_strategy | count_o_partitions | overall_size | child_relid | child_schemaname | child_tablename | child_owner | child_size |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 16389 | test | parted | stefanie | LIST | 3 | 0 | 16396 | test | parted_part_1 | stefanie | 0 |
| 16389 | test | parted | stefanie | LIST | 3 | 0 | 16406 | test | parted_part_2 | stefanie | 0 |
| 16389 | test | parted | stefanie | LIST | 3 | 0 | 16416 | test | parted_part_3 | stefanie | 0 |
| 16441 | test | parted_test2 | stefanie | HASH | 0 | 0 |