skip to content

In PostgreSQL, why does creating the March partition of clicks fail when its DEFAULT partition already holds late March rows, and how do you recover and prevent it?

level: seniorimportance: should knowfreq 40%

answer

  1. the default's constraint shrinks
  2. scan, never move
  3. 23514 on the default partition
  4. move rows, then attach, one transaction
  5. alert on any row in the default

basics

~20 s

Adding a partition narrows what the DEFAULT partition may hold, so PostgreSQL scans the default and fails with SQLSTATE 23514 if any row belongs to the new range. Move those rows into a standalone table and attach it, in one transaction.

solid answer

~40 s

The DEFAULT partition's implicit constraint is "in no other partition". `CREATE TABLE clicks_2026_03 PARTITION OF clicks ...` shrinks that constraint, so PostgreSQL scans `clicks_default` to prove it holds no March rows; it never moves them. Finding one, it raises `updated partition constraint for default partition "clicks_default" would be violated by some row` (SQLSTATE `23514`). The scan runs under the command's locks, and `PARTITION OF` holds `ACCESS EXCLUSIVE` on the parent, so all clickstream traffic waits even when it succeeds. Recover in one transaction: create the March table standalone, `DELETE ... RETURNING` the March rows from the default into it, then `ATTACH PARTITION`. Prevent it by creating partitions ahead, keeping the default empty and alerted on, and making `clicked_at` `NOT NULL`. Hash-partitioned tables cannot have a default.

code

sql · 16 lines
sql
BEGIN;

CREATE TABLE clicks_2026_03 (LIKE clicks INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

WITH moved AS (
    DELETE FROM clicks_default
    WHERE clicked_at >= '2026-03-01 00:00:00+00'
      AND clicked_at <  '2026-04-01 00:00:00+00'
    RETURNING *
)
INSERT INTO clicks_2026_03 SELECT * FROM moved;

ALTER TABLE clicks ATTACH PARTITION clicks_2026_03
    FOR VALUES FROM ('2026-03-01 00:00:00+00') TO ('2026-04-01 00:00:00+00');

COMMIT;

go deeper

for a junior

Know that a DEFAULT partition catches rows no other partition accepts, including NULL range keys, and that hash-partitioned tables cannot have one.

for a middle

Explain why adding a partition scans the default, why it fails instead of moving rows, and what SQLSTATE 23514 means here.

for a senior

Show the atomic repair (move rows, attach, one transaction), the lock impact of the scan on a busy parent, and the monitoring that keeps the default empty.

for a principal

Decide per table whether a default is worth having: data loss on insert failure versus a silent pile-up that blocks future partitions, and who gets paged for each.

## The failure The clickstream table `clicks` is `PARTITION BY RANGE (clicked_at)` with monthly partitions and a catch-all created with `CREATE TABLE clicks_default PARTITION OF clicks DEFAULT`. Late March clicks landed in the default because no March partition existed yet. The nightly job then runs: ```sql CREATE TABLE clicks_2026_03 PARTITION OF clicks FOR VALUES FROM ('2026-03-01') TO ('2026-04-01'); ``` and gets `ERROR: updated partition constraint for default partition "clicks_default" would be violated by some row`, SQLSTATE `23514` (`check_violation`). Nothing is created. ## Why PostgreSQL checks the default - A **DEFAULT partition** has no bounds of its own. Its implicit partition constraint is "not inside any other partition's bounds". - Adding a partition **shrinks** that constraint, so PostgreSQL must prove the default holds no rows in the new range. It does that by **scanning the default partition**. - It does **not move rows**. If any row belongs to the new range, the command fails. - The scan is skipped only when the default is a foreign table, or carries a constraint that proves it cannot hold rows for the new partition. - The scan runs while the command holds its locks. `CREATE TABLE ... PARTITION OF` takes `ACCESS EXCLUSIVE` on the parent, so every read and write on `clicks` waits for the scan even when it succeeds. `ATTACH PARTITION` takes a weaker lock on the parent but still holds `ACCESS EXCLUSIVE` on the default while scanning it. ## Recovering Because DDL is transactional in PostgreSQL, the repair can be one atomic unit: 1. Create the March table standalone with the parent's shape (`LIKE clicks`). 2. Move the March rows out of the default with a `DELETE ... RETURNING` feeding an `INSERT` into the new table. 3. `ATTACH PARTITION` the new table for March. The default no longer holds March rows, so its check passes; the new table is validated against its bound too, which a matching `CHECK` constraint added beforehand lets PostgreSQL skip on a large table. If anything fails, the whole transaction rolls back and the rows stay safely in the default. When pg_partman manages the set, `CALL partman.partition_data_proc('public.clicks');` does the same work in committed batches. ## Looking before you move anything Before repairing, find out what is actually parked in the default. A query such as `SELECT date_trunc('month', clicked_at) AS month, count(*) FROM clicks_default GROUP BY 1 ORDER BY 1;` shows whether you are dealing with a few late March rows, a gap of several months because partition creation stopped, or a scatter of absurd timestamps from misconfigured clients. Each needs a different fix: create the missing partitions and move the rows, restart and alert on the job that creates them, or reject the bad rows at ingestion and delete them here. ## The other DEFAULT rules worth knowing | Rule | What it means for clicks | |---|---| | Not available for HASH partitioning | `a hash-partitioned table may not have a default partition` | | No range includes NULL | a row with a NULL `clicked_at` goes to the default if one exists, and fails otherwise | | One default per partitioned table | a second one fails with `partition ... conflicts with existing default partition ...` | | A sub-partitioned default is checked recursively | each of its partitions is locked and scanned the same way | | Without any default | an unmatched row fails with `no partition of relation "clicks" found for row` (also `23514`) | ## Keeping it from recurring - Create partitions **ahead of time**, so the default only ever catches true outliers such as client clocks set years ahead. - Keep the default **empty and alerted on**: a scheduled `SELECT count(*) FROM clicks_default`, or pg_partman's `check_default()`, that pages on any row. - Declare `clicked_at` `NOT NULL`, and validate timestamps at ingestion, so garbage is rejected rather than parked. - If the default must legitimately hold a known kind of outlier, give it a `CHECK` constraint describing what may live there. A future partition whose range that constraint excludes can then be added without the scan. Whether a table should have a default at all (a loud failure versus a silent pile-up) is the engine-neutral trade-off covered with partition maintenance; the rules above are what decide how PostgreSQL's default actually behaves.

  • Why can a click with a NULL clicked_at end up in clicks_default?
    No range partition can contain NULL, so tuple routing treats a NULL range key as matching nothing. With a DEFAULT partition the row is stored there; without one the insert fails with `no partition of relation "clicks" found for row`. Declaring `clicked_at NOT NULL` stops it at the door.
  • Even when the default is empty, why can creating a new partition still stall the clickstream?
    `CREATE TABLE ... PARTITION OF` needs `ACCESS EXCLUSIVE` on the parent, and with a default present it also has to scan the default before committing. While it waits for the lock or scans a large default, all reads and writes on `clicks` queue behind it. Creating partitions well ahead, and keeping the default tiny, keeps that window short.
  • Can a hash-partitioned table use a DEFAULT partition as a safety net?
    No. PostgreSQL refuses it with `a hash-partitioned table may not have a default partition`. Every hash value maps to exactly one remainder, so once all remainders for the modulus exist there is nothing left for a default to catch.

A DEFAULT partition is a lost-and-found box. Before the office opens a new shelf for March items, someone must check the box for March items, and the shelf cannot open while one is still in it; nobody sorts the box for you.

  • A DEFAULT partition is allowed for HASH partitioning too, to catch unmatched hash values.
  • PostgreSQL moves the matching rows out of the default automatically when the new partition is created.
  • Creating a partition next to a default is metadata-only and never reads data.
  • A row with a NULL range key is rejected even when a default partition exists.
  • A table can have one default per range of values, for example one per year.