PolarDB for PostgreSQL(Compatible with Oracle) provides five system catalog views for querying partition and subpartition metadata.
| View | Scope |
|---|---|
ALL_PART_TABLES | Partitioned tables accessible in the current database |
ALL_TAB_PARTITIONS | Partitions in the current database |
ALL_TAB_SUBPARTITIONS | Subpartitions in the current database |
ALL_PART_KEY_COLUMNS | Partition key columns in the current database |
ALL_SUBPART_KEY_COLUMNS | Subpartition key columns in the current database |
Query a view
Use either of the following methods to query these views. Replace <viewname> with the actual view name.
SQL
SELECT * FROM <viewname>;For example, to list all partitioned tables with their partition type and count:
SELECT owner, table_name, partitioning_type, partition_count FROM ALL_PART_TABLES;psql
\d <viewname>ALL_PART_TABLES
ALL_PART_TABLES returns one row per partitioned table accessible in the current database.
| Column | Type | Description |
|---|---|---|
owner | TEXT | Owner of the partitioned table. |
schema_name | TEXT | Schema that contains the table. |
table_name | TEXT | 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 | Number of partitions. |
def_subpartition_count | INTEGER | The number of subpartitions in the table. |
partitioning_key_count | INTEGER | Number of partition key columns. |
subpartitioning_key_count | INTEGER | Number of subpartition key columns. |
status | CHARACTER VARYING(8) | Included for compatibility only. Always VALID. |
def_tablespace_name | CHARACTER VARYING(30) | Included for compatibility only. Always NULL. |
def_pct_free | NUMERIC | Included for compatibility only. Always NULL. |
def_pct_used | NUMERIC | Included for compatibility only. Always NULL. |
def_ini_trans | NUMERIC | Included for compatibility only. Always NULL. |
def_max_trans | NUMERIC | Included for compatibility only. Always NULL. |
def_initial_extent | CHARACTER VARYING(40) | Included for compatibility only. Always NULL. |
def_next_extent | CHARACTER VARYING(40) | Included for compatibility only. Always NULL. |
def_min_extents | CHARACTER VARYING(40) | Included for compatibility only. Always NULL. |
def_max_extents | CHARACTER VARYING(40) | Included for compatibility only. Always NULL. |
def_pct_increase | CHARACTER VARYING(40) | Included for compatibility only. Always NULL. |
def_freelists | NUMERIC | Included for compatibility only. Always NULL. |
def_freelist_groups | NUMERIC | Included for compatibility only. Always NULL. |
def_logging | CHARACTER VARYING(7) | Included for compatibility only. Always YES. |
def_compression | CHARACTER VARYING(8) | Included for compatibility only. Always NONE. |
def_buffer_pool | CHARACTER VARYING(7) | Included for compatibility only. Always DEFAULT. |
ref_ptn_constraint_name | CHARACTER VARYING(30) | Included for compatibility only. Always NULL. |
interval | CHARACTER VARYING(1000) | Included for compatibility only. Always NULL. |
ALL_TAB_PARTITIONS
ALL_TAB_PARTITIONS returns one row per partition in the current database.
| Column | Type | Description |
|---|---|---|
table_owner | TEXT | Owner of the table. |
schema_name | TEXT | Schema that contains the table. |
table_name | TEXT | Name of the table. |
composite | TEXT | Whether the table has subpartitions. YES: the table is subpartitioned. NO: the table is not subpartitioned. |
partition_name | TEXT | Name of the partition. |
subpartition_count | BIGINT | Number of subpartitions in this partition. |
high_value | TEXT | The high partitioning value specified in the CREATE TABLE statement. |
high_value_length | INTEGER | Length of high_value. |
partition_position | INTEGER | Included for compatibility only. Always NULL. |
tablespace_name | TEXT | Tablespace that contains the partition. |
pct_free | NUMERIC | Included for compatibility only. Always 0. |
pct_used | NUMERIC | Included for compatibility only. Always 0. |
ini_trans | NUMERIC | Included for compatibility only. Always 0. |
max_trans | NUMERIC | Included for compatibility only. Always 0. |
initial_extent | NUMERIC | Included for compatibility only. Always NULL. |
next_extent | NUMERIC | Included for compatibility only. Always NULL. |
min_extent | NUMERIC | Included for compatibility only. Always 0. |
max_extent | NUMERIC | Included for compatibility only. Always 0. |
pct_increase | NUMERIC | Included for compatibility only. Always 0. |
freelists | NUMERIC | Included for compatibility only. Always NULL. |
freelist_groups | NUMERIC | Included for compatibility only. Always NULL. |
logging | CHARACTER VARYING(7) | Included for compatibility only. Always YES. |
compression | CHARACTER VARYING(8) | Included for compatibility only. Always NONE. |
num_rows | NUMERIC | Estimated row count. Same as pg_class.reltuples. |
blocks | INTEGER | Number of disk pages. Same as pg_class.relpages. |
empty_blocks | NUMERIC | Included for compatibility only. Always NULL. |
avg_space | NUMERIC | Included for compatibility only. Always NULL. |
chain_cnt | NUMERIC | Included for compatibility only. Always NULL. |
avg_row_len | NUMERIC | Included for compatibility only. Always NULL. |
sample_size | NUMERIC | Included for compatibility only. Always NULL. |
last_analyzed | TIMESTAMP WITHOUT TIME ZONE | Included for compatibility only. Always NULL. |
buffer_pool | CHARACTER VARYING(7) | Included for compatibility only. Always NULL. |
global_stats | CHARACTER VARYING(3) | Included for compatibility only. Always YES. |
user_stats | CHARACTER VARYING(3) | Included for compatibility only. Always NO. |
backing_table | REGCLASS | Name of the partition's backing table. |
ALL_TAB_SUBPARTITIONS
ALL_TAB_SUBPARTITIONS returns one row per subpartition in the current database.
| Column | Type | Description |
|---|---|---|
table_owner | TEXT | Owner of the table. |
schema_name | TEXT | Schema that contains the table. |
table_name | TEXT | Name of the table. |
partition_name | TEXT | Name of the parent partition. |
subpartition_name | TEXT | Name of the subpartition. |
high_value | TEXT | The high subpartitioning value specified in the CREATE TABLE statement. |
high_value_length | INTEGER | Length of high_value. |
subpartition_position | INTEGER | Included for compatibility only. Always NULL. |
tablespace_name | TEXT | Tablespace that contains the subpartition. |
pct_free | NUMERIC | Included for compatibility only. Always 0. |
pct_used | NUMERIC | Included for compatibility only. Always 0. |
ini_trans | NUMERIC | Included for compatibility only. Always 0. |
max_trans | NUMERIC | Included for compatibility only. Always 0. |
initial_extent | NUMERIC | Included for compatibility only. Always NULL. |
next_extent | NUMERIC | Included for compatibility only. Always NULL. |
min_extent | NUMERIC | Included for compatibility only. Always 0. |
max_extent | NUMERIC | Included for compatibility only. Always 0. |
pct_increase | NUMERIC | Included for compatibility only. Always 0. |
freelists | NUMERIC | Included for compatibility only. Always NULL. |
freelist_groups | NUMERIC | Included for compatibility only. Always NULL. |
logging | CHARACTER VARYING(7) | Included for compatibility only. Always YES. |
compression | CHARACTER VARYING(8) | Included for compatibility only. Always NONE. |
num_rows | NUMERIC | Estimated row count. Same as pg_class.reltuples. |
blocks | INTEGER | Number of disk pages. Same as pg_class.relpages. |
empty_blocks | NUMERIC | Included for compatibility only. Always NULL. |
avg_space | NUMERIC | Included for compatibility only. Always NULL. |
chain_cnt | NUMERIC | Included for compatibility only. Always NULL. |
avg_row_len | NUMERIC | Included for compatibility only. Always NULL. |
sample_size | NUMERIC | Included for compatibility only. Always NULL. |
last_analyzed | TIMESTAMP WITHOUT TIME ZONE | Included for compatibility only. Always NULL. |
buffer_pool | CHARACTER VARYING(7) | Included for compatibility only. Always NULL. |
global_stats | CHARACTER VARYING(3) | Included for compatibility only. Always YES. |
user_stats | CHARACTER VARYING(3) | Included for compatibility only. Always NO. |
backing_table | REGCLASS | Name of the subpartition's backing table. |
ALL_PART_KEY_COLUMNS
ALL_PART_KEY_COLUMNS returns one row per partition key column in the current database.
| Column | Type | Description |
|---|---|---|
owner | TEXT | Owner of the partitioned table. |
schema_name | TEXT | Schema that contains the table. |
name | TEXT | Name of the partitioned table. |
object_type | CHARACTER(5) | Included for compatibility only. Always TABLE. |
column_name | TEXT | Name of the partition key column. |
column_position | INTEGER | Ordinal position of the column in the partition key. 1 = first key column, 2 = second key column, and so on. |
ALL_SUBPART_KEY_COLUMNS
ALL_SUBPART_KEY_COLUMNS returns one row per subpartition key column in the current database.
| Column | Type | Description |
|---|---|---|
owner | TEXT | Owner of the partitioned table. |
schema_name | TEXT | Schema that contains the table. |
name | TEXT | Name of the partitioned table. |
object_type | CHARACTER(5) | Included for compatibility only. Always TABLE. |
column_name | TEXT | Name of the subpartition key column. |
column_position | INTEGER | Ordinal position of the column in the subpartition key. 1 = first key column, 2 = second key column, and so on. |