pg_get_partkeydef()

A function returning the definition of a partition key

pg_get_partkeydef() is a system function returning the definition of a partitioning key.

pg_get_partkeydef() was added in PostgreSQL 10.

Usage

pg_get_partkeydef ( table oid ) → text

pg_get_partkeydef() extracts the partition key definition contained in pg_partitioned_table as text.

NULL is returned if the specified relation is not a partitioned table. An ERROR is raised if the specified relation does not exist:

Note that prior to PostgreSQL 16, pg_get_partkeydef() was not documented.

Change history

Examples

Basic usage example for pg_get_partkeydef():

postgres=# CREATE TABLE hash_partition_table (
             id INT NOT NULL,
             val TEXT
           ) PARTITION BY HASH (id);
CREATE TABLE

postgres=# SELECT pg_get_partkeydef('hash_partition_table'::regclass);
 pg_get_partkeydef 
-------------------
 HASH (id)
(1 row)

Using pg_get_partkeydef() with a table which is not partitioned:

postgres=# SELECT pg_get_partkeydef('foo'::regclass) IS NULL;
 ?column? 
----------
 t
(1 row)

An ERROR is raised if the specified table does not exist:

postgres=# SELECT pg_get_partkeydef('non_such_table'::regclass);
ERROR:  relation "non_such_table" does not exist
LINE 1: SELECT pg_get_partkeydef('non_such_table'::regclass);

Categories

Partitioning, System function

See also

pg_partitioned_table, Declarative partitioning