skip to content

In PostgreSQL, which rows does FOR VALUES FROM ('2026-03-01') TO ('2026-04-01') accept, and what happens to a row no partition accepts?

level: juniorimportance: must knowfreq 58%

answer

  1. one end in, one end out
  2. adjacent months share a boundary
  3. parent stores nothing
  4. no partition of relation found for row

basics

~20 s

FROM is inclusive and TO is exclusive, so the partition holds 2026-03-01 up to but not including 2026-04-01. A row that fits no partition fails with 'no partition of relation found for row' unless a DEFAULT partition exists.

solid answer

~40 s

In `CREATE TABLE clicks_2026_03 PARTITION OF clicks FOR VALUES FROM ('2026-03-01') TO ('2026-04-01')`, the lower bound is **inclusive** and the upper bound **exclusive**, so March and April can both use `2026-04-01` as a boundary without overlapping; truly overlapping bounds are refused. The parent `clicks` has no storage: every inserted row is routed to the partition whose bounds contain its `clicked_at`. If none does, the statement fails with `no partition of relation "clicks" found for row` (SQLSTATE `23514`), unless a `DEFAULT` partition exists to catch it. `NULL` is never inside a range, `MINVALUE`/`MAXVALUE` mean unbounded, and the bound literal is evaluated once at creation, so for a `timestamptz` key it is read in that session's `TimeZone`.

code

sql · 18 lines
sql
CREATE TABLE clicks (
    click_id    bigint      NOT NULL,
    clicked_at  timestamptz NOT NULL,
    campaign_id bigint      NOT NULL
) PARTITION BY RANGE (clicked_at);

SET TimeZone = 'UTC';
CREATE TABLE clicks_2026_03 PARTITION OF clicks
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE clicks_2026_04 PARTITION OF clicks
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

INSERT INTO clicks VALUES (1, '2026-04-01 00:00:00+00', 7);  -- routed to clicks_2026_04

SELECT tableoid::regclass AS partition, click_id FROM clicks;

INSERT INTO clicks VALUES (2, '2026-06-02 10:00:00+00', 7);
-- ERROR:  no partition of relation "clicks" found for row

go deeper

for a junior

Recall that FROM is inclusive and TO exclusive, that the parent stores no rows, and that an unmatched row raises 'no partition of relation found for row' unless a DEFAULT partition exists.

for a middle

Explain routing, NULL keys going to the default, row movement on UPDATE, MINVALUE and MAXVALUE, and why a timestamptz bound depends on the creating session's TimeZone.

for a senior

Show the operational habits: create partitions from a UTC session well ahead of time, alert before the newest partition runs out, and keep the key NOT NULL so nothing silently lands in a default.

for a principal

Frame partition creation as an availability dependency of every write path, and decide who owns it: application migrations, a scheduled job, or an extension such as pg_partman.

## Declaring the table and its partitions PostgreSQL's declarative partitioning starts with a **partitioned table** and then one `CREATE TABLE ... PARTITION OF` per partition: ```sql CREATE TABLE clicks (...) PARTITION BY RANGE (clicked_at); CREATE TABLE clicks_2026_03 PARTITION OF clicks FOR VALUES FROM ('2026-03-01') TO ('2026-04-01'); ``` - The parent `clicks` has **no storage of its own**. Every row inserted into it is **routed** to the partition whose bounds contain the row's partition key value. - Each **partition** is an ordinary table with exactly the parent's columns. You cannot add extra columns to one partition. - Behind the scenes partitions are linked to the parent through table inheritance, so the `tableoid` system column tells you which partition a row physically lives in. ## How range bounds work - `FROM` is **inclusive** and `TO` is **exclusive**. `clicks_2026_03` accepts `2026-03-01 00:00:00` and everything up to, but not including, `2026-04-01 00:00:00`. - Adjacent months therefore share a boundary value: March ends `TO ('2026-04-01')` and April starts `FROM ('2026-04-01')` without overlapping. This is why monthly bounds are written as first-of-month to first-of-next-month, never as `'2026-03-31 23:59:59'`. - Genuinely overlapping bounds are refused with `partition "..." would overlap partition "..."`. - `MINVALUE` and `MAXVALUE` mean **unbounded**: `FROM (MINVALUE) TO ('2026-01-01')` holds everything earlier. They are not storable values, unlike the timestamp value `'infinity'`. - With a multi-column range key, bounds compare **row-wise**: for `PARTITION BY RANGE (x, y)`, `FROM (1, 2) TO (3, 4)` accepts `x = 2` with any non-null `y`. - The bound is an expression evaluated **once, when the partition is created**. For a `timestamptz` key, `'2026-03-01'` is interpreted in the creating session's `TimeZone` setting, so a partition created from a session in another zone gets boundaries shifted by that offset. Create partitions from a UTC session, or write the offset explicitly (`'2026-03-01 00:00:00+00'`). ## A row that fits nowhere - With no matching partition, the `INSERT` fails: `ERROR: no partition of relation "clicks" found for row`, with a `DETAIL` that shows the key, and SQLSTATE `23514` (`check_violation`). The whole statement rolls back, including the other rows of a multi-row insert. - With a `DEFAULT` partition, the row is stored there instead. That trades a loud failure for a quiet pile-up, which has its own rules. - `NULL` is never inside a range. A row with a `NULL` `clicked_at` goes to the default partition if there is one and fails otherwise, so declare the key column `NOT NULL`. - An `UPDATE` that changes `clicked_at` to a value in another month **moves** the row into the partition that now matches. ## The bound syntax of the other two methods | Method | Parent declaration | One partition's bound | |---|---|---| | RANGE | `PARTITION BY RANGE (clicked_at)` | `FOR VALUES FROM (...) TO (...)` | | LIST | `PARTITION BY LIST (country_code)` | `FOR VALUES IN ('DE', 'AT', 'CH')`; at most one list partition may also accept `NULL` | | HASH | `PARTITION BY HASH (click_id)` | `FOR VALUES WITH (MODULUS 8, REMAINDER 3)`; no `DEFAULT` partition is allowed | Any of the three can also be given a `DEFAULT` partition with `PARTITION OF clicks DEFAULT`, except `HASH`. ## Things that catch people 1. There is no way to turn an existing ordinary table into a partitioned table in place. You create a new partitioned table and either copy the data in or attach the old table as one of its partitions. 2. A primary key or unique constraint on `clicks` must include `clicked_at`, because PostgreSQL has no index spanning partitions. 3. Partitions are easy to list: `\d+ clicks` in psql shows them with their bounds, and `SELECT * FROM pg_partition_tree('clicks');` returns one row per member of the tree with its parent, level and whether it is a leaf. 4. Creating the partitions ahead of time is your job (or a tool's, such as pg_partman). PostgreSQL never creates a missing partition on insert.

  • How do you see which partition a particular click row was routed to?
    Select the `tableoid` system column cast to `regclass`: `SELECT tableoid::regclass, * FROM clicks WHERE click_id = 1;`. Partitions are inheritance children of the parent behind the scenes, so every row carries the OID of the table it is physically stored in, and the cast prints that partition's name.
  • Can you convert the existing unpartitioned clicks table into a partitioned one with ALTER TABLE?
    No. PostgreSQL cannot turn a regular table into a partitioned table, or back. Create a new partitioned table, then either copy the data into new partitions or attach the old table as one partition covering its date range. Attaching validates the rows against the bound, which a matching CHECK constraint lets it skip.

  • TO is inclusive, so the March partition also takes midnight on April 1.
  • Rows that match no partition are stored in the parent table itself.
  • A RANGE partition can be declared to accept NULL keys.
  • PostgreSQL creates the missing monthly partition automatically when a row needs it.
  • Overlapping bounds are allowed and the partition created first wins.
  • An existing regular table can be converted to a partitioned table in place.