Add a partition to an existing partitioned table - PolarDB

Add a new partition to an existing partitioned table using CREATE TABLE ... PARTITION OF. PolarDB for PostgreSQL supports RANGE, LIST, and HASH partitioning, and is fully compatible with PostgreSQL. For the complete syntax reference, see the PostgreSQL documentation.

Prerequisites

Before you begin, ensure that you have:

A partitioned table with a defined partitioning strategy (RANGE, LIST, or HASH)
The necessary permissions to create tables in your database

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 ]

The syntax above applies to RANGE and LIST partitions. For HASH partitions, use ALTER TABLE instead:

ALTER TABLE <table_name> ADD PARTITION <partition_name>;

Examples

The examples below use pre-existing parent tables. Each parent table is defined with a specific partitioning method—create the parent table first before adding partitions.

Create a single-level partition

Range partition

Use range partitioning when data falls into ordered ranges, such as dates, IDs, or numeric values. Each partition covers a contiguous range of values.

Parent table definition:

CREATE TABLE tab_range (
    id    integer NOT NULL,
    value integer
) PARTITION BY RANGE (value);

Add a partition:

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

List partition

Use list partitioning when data maps to a known set of discrete values, such as regions, categories, or status codes.

Parent table definition:

CREATE TABLE tab_list (
    id    integer NOT NULL,
    value integer
) PARTITION BY LIST (value);

Add a partition:

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

Hash partition

Use hash partitioning to distribute data evenly across a fixed number of partitions when no natural range or list grouping exists. Specify modulus (total number of partitions) and remainder (this partition's slot).

Hash partitions use ALTER TABLE syntax instead of CREATE TABLE ... PARTITION OF.

Parent table definition:

CREATE TABLE tab_hash (
    id    integer NOT NULL,
    value integer
) PARTITION BY HASH (value);

Add a partition:

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

Create a multi-level partition

PolarDB for PostgreSQL lets you divide a partition into subpartitions by appending a PARTITION BY clause when creating the child partition. You can combine partitioning methods across levels.

Multi-level range partition

CREATE TABLE tab_range_p2 PARTITION OF tab_range FOR VALUES FROM (100) TO (200) PARTITION BY LIST (value);

Multi-level list partition

CREATE TABLE tab_list_p2 PARTITION OF tab_list FOR VALUES IN (40, 50, 60) PARTITION BY HASH (value);

Multi-level hash partition

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

Verify that the partition was created

After creating a partition, query the parent table to confirm that data routes to the correct partition:

-- Insert a row into the parent table
INSERT INTO tab_range (id, value) VALUES (1, 50);

-- Query the parent table — PostgreSQL automatically routes to the matching partition
SELECT * FROM tab_range WHERE value < 100;

-- Query the partition directly
SELECT * FROM tab_range_p1;

Both queries return the inserted row, confirming that the partition is active.