pg_partitioned_table

A system table storing table partitioning information

pg_partitioned_table is a system catalogue table storing table partitioning information for tables defined in pg_class with relkind 'p'.

pg_partitioned_table was added in PostgreSQL 10.

Definition by PostgreSQL version

pg_partitioned_table (PostgreSQL 15)

            Table "pg_catalog.pg_partitioned_table"
    Column     |     Type     | Collation | Nullable | Default 
---------------+--------------+-----------+----------+---------
 partrelid     | oid          |           | not null | 
 partstrat     | "char"       |           | not null | 
 partnatts     | smallint     |           | not null | 
 partdefid     | oid          |           | not null | 
 partattrs     | int2vector   |           | not null | 
 partclass     | oidvector    |           | not null | 
 partcollation | oidvector    |           | not null | 
 partexprs     | pg_node_tree | C         |          | 
Indexes:
    "pg_partitioned_table_partrelid_index" PRIMARY KEY, btree (partrelid)
    

Documentation: pg_partitioned_table

pg_partitioned_table (PostgreSQL 14)

            Table "pg_catalog.pg_partitioned_table"
    Column     |     Type     | Collation | Nullable | Default 
---------------+--------------+-----------+----------+---------
 partrelid     | oid          |           | not null | 
 partstrat     | "char"       |           | not null | 
 partnatts     | smallint     |           | not null | 
 partdefid     | oid          |           | not null | 
 partattrs     | int2vector   |           | not null | 
 partclass     | oidvector    |           | not null | 
 partcollation | oidvector    |           | not null | 
 partexprs     | pg_node_tree | C         |          | 
Indexes:
    "pg_partitioned_table_partrelid_index" PRIMARY KEY, btree (partrelid)
    

Documentation: pg_partitioned_table

pg_partitioned_table (PostgreSQL 13)

            Table "pg_catalog.pg_partitioned_table"
    Column     |     Type     | Collation | Nullable | Default 
---------------+--------------+-----------+----------+---------
 partrelid     | oid          |           | not null | 
 partstrat     | "char"       |           | not null | 
 partnatts     | smallint     |           | not null | 
 partdefid     | oid          |           | not null | 
 partattrs     | int2vector   |           | not null | 
 partclass     | oidvector    |           | not null | 
 partcollation | oidvector    |           | not null | 
 partexprs     | pg_node_tree | C         |          | 
Indexes:
    "pg_partitioned_table_partrelid_index" UNIQUE, btree (partrelid)
    

Documentation: pg_partitioned_table

pg_partitioned_table (PostgreSQL 12)

            Table "pg_catalog.pg_partitioned_table"
    Column     |     Type     | Collation | Nullable | Default 
---------------+--------------+-----------+----------+---------
 partrelid     | oid          |           | not null | 
 partstrat     | "char"       |           | not null | 
 partnatts     | smallint     |           | not null | 
 partdefid     | oid          |           | not null | 
 partattrs     | int2vector   |           | not null | 
 partclass     | oidvector    |           | not null | 
 partcollation | oidvector    |           | not null | 
 partexprs     | pg_node_tree | C         |          | 
Indexes:
    "pg_partitioned_table_partrelid_index" UNIQUE, btree (partrelid)
    

Documentation: pg_partitioned_table

pg_partitioned_table (PostgreSQL 11)

            Table "pg_catalog.pg_partitioned_table"
    Column     |     Type     | Collation | Nullable | Default 
---------------+--------------+-----------+----------+---------
 partrelid     | oid          |           | not null | 
 partstrat     | "char"       |           | not null | 
 partnatts     | smallint     |           | not null | 
 partdefid     | oid          |           | not null | 
 partattrs     | int2vector   |           | not null | 
 partclass     | oidvector    |           | not null | 
 partcollation | oidvector    |           | not null | 
 partexprs     | pg_node_tree |           |          | 
Indexes:
    "pg_partitioned_table_partrelid_index" UNIQUE, btree (partrelid)
    

Documentation: pg_partitioned_table

pg_partitioned_table (PostgreSQL 10)

            Table "pg_catalog.pg_partitioned_table"
    Column     |     Type     | Collation | Nullable | Default 
---------------+--------------+-----------+----------+---------
 partrelid     | oid          |           | not null | 
 partstrat     | "char"       |           | not null | 
 partnatts     | smallint     |           | not null | 
 partattrs     | int2vector   |           | not null | 
 partclass     | oidvector    |           | not null | 
 partcollation | oidvector    |           | not null | 
 partexprs     | pg_node_tree |           |          | 
Indexes:
    "pg_partitioned_table_partrelid_index" UNIQUE, btree (partrelid)
    

Documentation: pg_partitioned_table

Change history

Examples

After creating the sample table from the PostgreSQL partitioning documentation:

postgres=# CREATE TABLE measurement (
               city_id         int not null,
               logdate         date not null,
               peaktemp        int,
               unitsales       int
           ) PARTITION BY RANGE (logdate);
CREATE TABLE

the following entry is added to pg_partitioned_table:

postgres=# SELECT * FROM pg_partitioned_table WHERE partrelid='measurement'::REGCLASS\gx
-[ RECORD 1 ]-+------
partrelid     | 17824
partstrat     | r
partnatts     | 1
partdefid     | 0
partattrs     | 2
partclass     | 3122
partcollation | 0
partexprs     |

Categories

Partitioning, System catalogue