System views for partitioned tables - PolarDB - Alibaba Cloud Documentation Center

PolarDB for PostgreSQL (Compatible with Oracle) provides system views for you to view the structure of a partition table.

PolarDB for PostgreSQL (Compatible with Oracle) provides the following system views and functions for you to view information about partition tables in your database.

pg_partitioned_table

Columns

Column	Description
partrelid	The OID of the pg_class entry for this partitioned table.
partstrat	The partitioning strategy. Valid values: h: hash-partitioned table. l: list-partitioned table. r: range-partitioned table.
partnatts	The number of columns in the partition key.
partdefid	The OID of the pg_class entry for the default partition of this partitioned table. or zero if the partitioned table does not have a default partition.
partattrs	An array of partnatts values that indicate which table columns are part of the partition key. For example, a value of `1 3` indicates that the first and the third table columns make up the partition key. A zero in this array indicates that the corresponding partition key column is an expression, rather than a simple column reference.
partclass	For each column in the partition key, this contains the OID of the operator class to use.
partcollation	For each column in the partition key, this contains the OID of the collation to use for partitioning, or zero if the column is not of a collatable data type.
partexprs	Expression tree (`nodeToString()`) for partition key columns that are not simple column references. This is a list with one element for each zero entry in partattrs. The value is null if all partition key columns are simple references.

Examples

select * from  pg_partitioned_table;
 partrelid | partstrat | partnatts | partdefid | partattrs | partclass | partcollation | partexprs | partnullorder 
-----------+-----------+-----------+-----------+-----------+-----------+---------------+-----------+---------------
     17124 | h         |         1 |         0 | 1         | 10028     | 0             |           |             0
(1 row)

pg_partition_tree

This function lists the tables or indexes in the partition tree of the given partitioned table or partitioned index, with one row for each partition. The argument of the function is the table name. The following table describes the return columns.

Columns

Column	Description
relid	The name of the partition.
parentrelid	The name of its immediate parent partition. If the partition has no immediate parent partition, this field is null.
isleaf	Whether the partition is a leaf partition.
level	The level of the partition in the hierarchy. The level value is `0` for the input table or index as the root, `1` for its immediate child partitions, and `2` for their partitions, and so on.

Examples

select * from pg_partition_tree('idxpart');
  relid   | parentrelid | isleaf | level 
----------+-------------+--------+-------
 idxpart  |             | f      |     0
 idxpart0 | idxpart     | t      |     1
 idxpart1 | idxpart     | t      |     1
(3 rows)

pg_class

The pg_class catalog contains tables and other objects that have columns or are otherwise similar to a table.

Columns

Column	Description
relkind	p =partitioned table, I =partitioned index
relhassubclass	True if the table or index has (or once had) any inheritance children or partitions
relispartition	True if the table or index is a partition
relpartbound	Internal representation of the partition bound if the table is a partition (see relispartition).

Examples

select relkind , relhassubclass, relispartition, pg_catalog.pg_get_expr(relpartbound ,oid) as relpartbound, relpartname from pg_class where relname = 'sales_q1_2012';
 relkind | relhassubclass | relispartition |                     relpartbound                     | relpartname 
---------+----------------+----------------+------------------------------------------------------+-------------
 r       | f              | t              | FOR VALUES FROM (MINVALUE) TO ('01-APR-12 00:00:00') | q1_2012
(1 row)

pg_inherits

The pg_inherits catalog records information about table and index inheritance hierarchies. There is one entry for each direct parent-child table or index relationship in the database.

Columns

Column	Description
inhrelid	The OID of the partition.
inhparent	The OID of the direct parent partition.
inhseqno	If there is more than one direct parent for a child table (multiple inheritance), this number indicates the order in which the inherited columns are to be arranged. The count starts at 1. Indexes cannot have multiple inheritance, since they can only inherit when using declarative partitioning.
inhdetachpending	True for a partition that is in the process of being detached

Examples

select * from pg_inherits where inhrelid = 17136;
 inhrelid | inhparent | inhseqno 
----------+-----------+----------
    17136 |     17133 |        1
(1 row)