Table partitioning

Goal

Reference for table partitioning: RANGE, LIST, partition management, and runtime behavior.

PARTITION BY RANGE

CREATE TABLE events (
 id INTEGER PRIMARY KEY,
 ts DATE NOT NULL,
 data TEXT
) PARTITION BY RANGE (ts);

Range partitions

CREATE TABLE events_2025 PARTITION OF events
 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE events_2026 PARTITION OF events
 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

PARTITION BY LIST

CREATE TABLE metrics (
 id INTEGER PRIMARY KEY,
 region VARCHAR(20) NOT NULL,
 value BIGINT
) PARTITION BY LIST (region);

List partitions

CREATE TABLE metrics_eu PARTITION OF metrics
 FOR VALUES IN ('eu-west', 'eu-east');

CREATE TABLE metrics_us PARTITION OF metrics
 FOR VALUES IN ('us-east', 'us-west');

DEFAULT partition

CREATE TABLE events_other PARTITION OF events DEFAULT;

Rows that do not fit into any partition are routed to DEFAULT. If DEFAULT does not exist and no suitable partition is found — 23514 check_violation.

ALTER TABLE — attach / detach

ALTER TABLE events ATTACH PARTITION events_2027
 FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');

ALTER TABLE events DETACH PARTITION events_2025;

DROP PARTITION

DROP PARTITION events_2025;

Runtime behavior

INSERT routing

An INSERT into the parent table automatically routes the row to the appropriate child partition:

INSERT INTO events (id, ts, data) VALUES (1, '2026-06-15', 'test');
-- → events_2026

If no partition matches and DEFAULT is missing:

ERROR: 23514: new row for relation "events" violates check constraint

Append-only inheritance

If the parent table is declared as append_only = true:

• All attached partitions inherit the append-only mode.
• A partition cannot disable append_only while the parent remains append-only — 42809.

CREATE TABLE events (
 id INTEGER PRIMARY KEY,
 ts DATE NOT NULL
) PARTITION BY RANGE (ts) WITH (append_only = true);

-- partitions inherit append_only
CREATE TABLE events_2026 PARTITION OF events
 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

Current v0 bounds

Unsupported forms return 0A000 feature_not_supported.

Expected SQLSTATE

Links

• DDL reference: ddl.md
• DML (INSERT routing): dml.md
• SQL compatibility overview: overview.md
• Known issues: Known issues