Table Partition Methods LIST · IN · DEFAULT

LIST Partition

Partition a table by discrete values — region, status, tenant. Each partition holds rows matching one or more specific values; DEFAULT catches unexpected ones.

What list partitioning is

List partitioning splits a table by a fixed set of discrete values. Each partition is associated with one or more specific values; rows are routed by exact match. It's the natural fit for categorical data — region, country, status, language code — anywhere the partition key is a small set of named buckets rather than a range.

When to choose LIST

The partition column has a small number of distinct values.
Rows partition naturally into named groups (regions, statuses, tenants).
Queries usually include WHERE col = '...' on that column.

Syntax

-- Parent
CREATE TABLE parent_table (...)
    PARTITION BY LIST (partition_column);

-- Each child
CREATE TABLE child_table
    PARTITION OF parent_table
    FOR VALUES IN (value1, value2, ...);

Example 1 — sales by region

SQL — parent

CREATE TABLE sales_data2 (
    sales_id  INT,
    amount    NUMERIC,
    branch    TEXT,
    region    TEXT
) PARTITION BY LIST (region);

SQL — children, one per region

CREATE TABLE sales_region_north
    PARTITION OF sales_data2 FOR VALUES IN ('North');

CREATE TABLE sales_region_south
    PARTITION OF sales_data2 FOR VALUES IN ('South');

CREATE TABLE sales_region_east
    PARTITION OF sales_data2 FOR VALUES IN ('East');

CREATE TABLE sales_region_west
    PARTITION OF sales_data2 FOR VALUES IN ('West');

SQL — insert & query

INSERT INTO sales_data2 VALUES
(1, 1500, 'Branch A', 'North'),
(2, 2200, 'Branch B', 'South'),
(3,  900, 'Branch C', 'East'),
(4, 3400, 'Branch D', 'West');

-- Pruning: only sales_region_north is scanned
SELECT SUM(amount) FROM sales_data2 WHERE region = 'North';

Example 2 — multiple values per partition

One partition can hold several values. Group similar buckets together:

SQL

-- Two regions in one partition
CREATE TABLE sales_region_eastwest
    PARTITION OF sales_data2 FOR VALUES IN ('East', 'West');

-- Three statuses sharing storage
CREATE TABLE orders_in_progress
    PARTITION OF orders FOR VALUES IN ('pending', 'processing', 'on_hold');

Useful when several values have similar volume and you don't need them physically separated.

DEFAULT partition for unknown values

Inserting a row whose partition value isn't in any list raises an error. Add a DEFAULT partition to catch new or unexpected values:

SQL

CREATE TABLE sales_region_other
    PARTITION OF sales_data2 DEFAULT;

-- Now an unexpected region just lands in 'other'
INSERT INTO sales_data2 VALUES (99, 100, 'Branch X', 'Antarctic');
-- → sales_region_other

List vs Range vs Hash

Use LIST when	Use RANGE when	Use HASH when
Values are discrete and small in number	Values are continuous (dates, amounts)	Values have no natural grouping
"Partition by region/status/tenant"	"Partition by month/year"	"Partition by user_id evenly"
You want named partitions	You want ordered partitions	You want load balance

Adding partitions later

New region appears? Add a partition for it. The parent table is untouched; existing partitions are untouched.

SQL

CREATE TABLE sales_region_central
    PARTITION OF sales_data2 FOR VALUES IN ('Central');

Note: if rows for 'Central' are currently sitting in the DEFAULT partition, the new partition can't be created until they're moved out.

Recap

List partitions hold rows whose partition column equals one of the listed values.
Each partition can list multiple values — group similar buckets together.
Unmatched values error out unless you have a DEFAULT partition.
Best for categorical data with a small, stable set of distinct values.