Create a partition

You can create a partition in an existing partitioned table. This topic describes the statement and examples for creating a partition.

Syntax

CREATE TABLE [ IF NOT EXISTS ] table_name
    PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ] { FOR VALUES partition_bound_spec | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ TABLESPACE tablespace_name ]

Note

The statement in PolarDB for PostgreSQL is fully compatible with that in PostgreSQL. For more information, see CREATE TABLE SQL Commands of PostgreSQL documentation.

Examples

Create a range partition

CREATE TABLE tab_range_p1 PARTITION OF tab_range FOR VALUES FROM (minvalue) TO (100);

Create a list partition

CREATE TABLE tab_list_p1 PARTITION OF tab_list FOR VALUES IN (10, 20, 30);

Create a hash partition

CREATE TABLE tab_hash_0 PARTITION OF tab_hash FOR VALUES WITH (modulus 2, remainder 1);

Create a multi-level partition

PolarDB for PostgreSQL allows you to further divide a partition into subpartitions and use a combination of partitioning strategies.

Create a multi-level range partition

CREATE TABLE tab_range_p1 PARTITION OF tab_range FOR VALUES FROM (minvalue) TO (100) PARTITION BY List (value);

Create a multi-level list partition

CREATE TABLE tab_list_p1 PARTITION OF tab_list FOR VALUES IN (10, 20, 30) PARTITION BY hash (value);

Create a multi-level hash partition

CREATE TABLE tab_hash_0 PARTITION OF tab_hash FOR VALUES WITH (modulus 2, remainder 1) PARTITION BY range (value);