PolarDB for PostgreSQL(Compatible with Oracle) provides five system catalog views that you can use to query the information about partitioned tables.
Query views for partitioned tables
PolarDB for PostgreSQL(Compatible with Oracle) provides the following two methods for you to query views for partitioned tables:
- Execute the SELECT statement.
Use the following query method:
SELECT * FROM viewname; - Use a PSQL client.
Run the following query command:
\d viewname
In the preceding command, viwename specifies the view name. You can use the following views to query the information about partitions and subpartitions:
ALL_PART_TABLES
The ALL_PART_TABLES view provides the information about all the partitioned tables in the current database.
| Parameter | Type | Description |
|---|---|---|
| owner | TEXT | The owner of a partitioned table. |
| schema_name | TEXT | The name of the schema to which the table belongs. |
| table_name | TEXT | The name of the table. |
| partitioning_type | TEXT | The partition type used to define table partitions. |
| subpartitioning_type | TEXT | The subpartition type used to define table subpartitions. |
| partition_count | BIGINT | The number of partitions in the table. |
| def_subpartition_count | INTEGER | The number of subpartitions in the table. |
| partitioning_key_count | INTEGER | The number of specified partition keys. |
| subpartitioning_key_count | INTEGER | The number of specified subpartition keys. |
| status | CHARACTER VARYING(8) | This parameter is supported for compatibility only. The value is VALID. |
| def_tablespace_name | CHARACTER VARYING(30) | This parameter is supported for compatibility only. The value is NULL. |
| def_pct_free | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| def_pct_used | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| def_ini_trans | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| def_max_trans | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| def_initial_extent | CHARACTER VARYING(40) | This parameter is supported for compatibility only. The value is NULL. |
| Def_next_extent | CHARACTER VARYING(40) | This parameter is supported for compatibility only. The value is NULL. |
| def_min_extents | CHARACTER VARYING(40) | This parameter is supported for compatibility only. The value is NULL. |
| def_max_extents | CHARACTER VARYING(40) | This parameter is supported for compatibility only. The value is NULL. |
| def_pct_increase | CHARACTER VARYING(40) | This parameter is supported for compatibility only. The value is NULL. |
| def_freelists | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| def_freelist_groups | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| def_logging | CHARACTER VARYING(7) | This parameter is supported for compatibility only. The value is YES. |
| def_compression | CHARACTER VARYING(8) | This parameter is supported for compatibility only. The value is NONE. |
| def_buffer_pool | CHARACTER VARYING(7) | This parameter is supported for compatibility only. The value is DEFAULT. |
| ref_ptn_constraint_name | CHARACTER VARYING(30) | This parameter is supported for compatibility only. The value is NULL. |
| interval | CHARACTER VARYING(1000) | This parameter is supported for compatibility only. The value is NULL. |
ALL_TAB_PARTITIONS
The ALL_TAB_PARTITIONS view provides the information about all the partitions in the current database.
| Parameter | Type | Description |
|---|---|---|
| table_owner | TEXT | The owner of the table to which the partition belongs. |
| schema_name | TEXT | The name of the schema to which the table belongs. |
| table_name | TEXT | The name of the table. |
| composite | TEXT |
|
| partition_name | TEXT | The name of the partition. |
| subpartition_count | BIGINT | The number of subpartitions in the partition. |
| high_value | TEXT | The high partitioning value specified in the CREATE TABLE statement. |
| high_value_length | INTEGER | The length of the high partitioning value. |
| partition_position | INTEGER | This parameter is supported for compatibility only. The value is NULL. |
| tablespace_name | TEXT | The name of the tablespace where the partition is located. |
| pct_free | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| pct_used | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| ini_trans | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| max_trans | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| initial_extent | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| next_extent | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| min_extent | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| max_extent | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| pct_increase | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| freelists | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| freelist_groups | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| logging | CHARACTER VARYING(7) | This parameter is supported for compatibility only. The value is YES. |
| compression | CHARACTER VARYING(8) | This parameter is supported for compatibility only. The value is NONE. |
| num_rows | NUMERIC | The same as pg_class.reltuples. |
| blocks | INTEGER | The same as pg_class.relpages. |
| empty_blocks | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| avg_space | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| chain_cnt | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| avg_row_len | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| sample_size | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| last_analyzed | TIMESTAMP WITHOUT TIME ZONE | This parameter is supported for compatibility only. The value is NULL. |
| buffer_pool | CHARACTER VARYING(7) | This parameter is supported for compatibility only. The value is NULL. |
| global_stats | CHARACTER VARYING(3) | This parameter is supported for compatibility only. The value is YES. |
| user_stats | CHARACTER VARYING(3) | This parameter is supported for compatibility only. The value is NO. |
| backing_table | REGCLASS | The name of the partition backup table. |
ALL_TAB_SUBPARTITIONS
The ALL_TAB_SUBPARTITIONS view provides the information about all the subpartitions in the current database.
| Parameter | Type | Description |
|---|---|---|
| table_owner | TEXT | The owner of the table to which the subpartition belongs. |
| schema_name | TEXT | The name of the schema to which the table belongs. |
| table_name | TEXT | The name of the table. |
| partition_name | TEXT | The name of the partition. |
| subpartition_name | TEXT | The name of the subpartition. |
| high_value | TEXT | The high subpartitioning value specified in the CREATE TABLE statement. |
| high_value_length | INTEGER | The length of the high subpartitioning value. |
| subpartition_position | INTEGER | This parameter is supported for compatibility only. The value is NULL. |
| tablespace_name | TEXT | The name of the tablespace where the subpartition is located. |
| pct_free | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| pct_used | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| ini_trans | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| max_trans | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| initial_extent | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| next_extent | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| min_extent | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| max_extent | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| pct_increase | NUMERIC | This parameter is supported for compatibility only. The value is 0. |
| freelists | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| freelist_groups | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| logging | CHARACTER VARYING(7) | This parameter is supported for compatibility only. The value is YES. |
| compression | CHARACTER VARYING(8) | This parameter is supported for compatibility only. The value is NONE. |
| num_rows | NUMERIC | The same as pg_class.reltuples. |
| blocks | INTEGER | The same as pg_class.relpages. |
| empty_blocks | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| avg_space | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| chain_cnt | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| avg_row_len | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| sample_size | NUMERIC | This parameter is supported for compatibility only. The value is NULL. |
| last_analyzed | TIMESTAMP WITHOUT TIME ZONE | This parameter is supported for compatibility only. The value is NULL. |
| buffer_pool | CHARACTER VARYING(7) | This parameter is supported for compatibility only. The value is NULL. |
| global_stats | CHARACTER VARYING(3) | This parameter is supported for compatibility only. The value is YES. |
| user_stats | CHARACTER VARYING(3) | This parameter is supported for compatibility only. The value is NO. |
| backing_table | REGCLASS | The name of the subpartition backup table. |
ALL_PART_KEY_COLUMNS
The ALL_PART_KEY_COLUMNS view provides the information about the partition key columns in the current database.
| Parameter | Type | Description |
|---|---|---|
| owner | TEXT | The owner of a partitioned table. |
| schema_name | TEXT | The name of the schema to which the table belongs. |
| name | TEXT | The name of the table to which the column belongs. |
| object_type | CHARACTER(5) | This parameter is supported for compatibility only. The value is TABLE. |
| column_name | TEXT | The name of the column on which the key is defined. |
| column_position | INTEGER | The ordinal position of this column. For example, a value of 1 indicates the first column and a value of 2 indicates the second column. All columns follow the same rule. |
ALL_SUBPART_KEY_COLUMNS
The ALL_SUBPART_KEY_COLUMNS view provides the information about the subpartition key columns in the current database.
| Parameter | Type | Description |
|---|---|---|
| owner | TEXT | The owner of the table. |
| schema_name | TEXT | The name of the schema to which the table belongs. |
| name | TEXT | The name of the table to which the column belongs. |
| object_type | CHARACTER(5) | This parameter is supported for compatibility only. The value is TABLE. |
| column_name | TEXT | The name of the column on which the key is defined. |
| column_position | INTEGER | The position of this column. For example, a value of 1 indicates the first column and a value of 2 indicates the second column. All columns follow the same rule. |