System views for partitioned tables-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for PostgreSQL provides the following system views and system functions for you to view information about partitioned tables in the database.

pg_partitioned_table

Column descriptions

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. Zero if the table has no default partition.
partattrs	An array of length partnatts indicating which table columns are part of the partition key. For example, a value of `1 3` indicates that the first and 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, the OID of the operator class to use.
partcollation	For each column in the partition key, the OID of the collation to use for partitioning. Zero if the column is not of a collatable data type.
partexprs	Expression trees for partition key columns that are not simple column references (`nodeToString()` expressions). This is a list with one element for each zero entry in partattrs. If all partition key columns are simple column references, this value is null.

Example

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

pg_partition_tree

Note

Only PolarDB for PostgreSQLPostgreSQL 14 supports this system view.

This function lists the tables or indexes in a partition tree. The input parameter is the table name. The following table describes the returned columns.

Column descriptions

Column	Description
relid	The name of the partition.
parentrelid	The name of the immediate parent partition. Null if there is no parent.
isleaf	Whether the partition is a leaf partition.
level	The level in the hierarchy. The value of level starts at `0`, indicating the input table or index as the root of the partition tree. `1` represents its partitions, `2` represents partitions of its partitions, and so on.

Example

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

pg_class stores all tables and indexes in PolarDB for PostgreSQL. Some of the information is related to partitioned tables. The following section describes the columns related to partitioned tables.

Column descriptions

Column	Description
relkind	The target object type. Valid values: r: ordinary table i: index S: sequence t: TOAST table v: view m: materialized view c: composite type f: foreign table p: partitioned table I: partitioned index
relhassubclass	True if the table or index has child partitions, False otherwise.
relispartition	True if the table or index is a partition.
relpartbound	If the table is a partition (see relispartition), the internal representation of the partition bound.

Example

SELECT relkind , relhassubclass, relispartition, pg_catalog.pg_get_expr(relpartbound ,oid) AS relpartbound 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

pg_inherits records information about table inheritance hierarchies. Each direct parent-child relationship in the database is recorded in this system view.

Column descriptions

Column	Description
inhrelid	The OID of the partition.
inhparent	The OID of the immediate parent partition.
inhseqno	The default value is 1 for partitioned tables. Otherwise, it represents an inheritance table.
inhdetachpending	True indicates a partition that is in the process of being detached. False otherwise. Note Only PolarDB for PostgreSQLPostgreSQL 14 supports this column.

Example

SELECT * FROM pg_inherits WHERE inhrelid = 17136;
 inhrelid | inhparent | inhseqno | inhdetachpending 
----------+-----------+----------+------------------
    17136 |     17133 |        1 | f
(1 row)