Default partition pitfall: new partition fails with 'would be violated by some row'

Command

-- Which periods have rows in the default partition?
SELECT date_trunc('month', created_at)::date AS month, count(*) AS row_count
FROM sales_default
GROUP BY 1
ORDER BY 1;

-- Move the rows into a new partition and attach it in one transaction
BEGIN;
SET LOCAL lock_timeout = '5s';
LOCK TABLE sales_default IN ACCESS EXCLUSIVE MODE;
CREATE TABLE sales_2027_02 (LIKE sales INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
WITH moved AS (
  DELETE FROM sales_default
  WHERE created_at >= DATE '2027-02-01' AND created_at < DATE '2027-03-01'
  RETURNING *
)
INSERT INTO sales_2027_02 SELECT * FROM moved;
ALTER TABLE sales ATTACH PARTITION sales_2027_02
  FOR VALUES FROM ('2027-02-01') TO ('2027-03-01');
COMMIT;

What it does

Counts rows that landed in the default partition per period; moves rows within the new partition's range into a new table and attaches it, all in one transaction.

When to use it

When CREATE TABLE ... PARTITION OF or ATTACH fails with 'updated partition constraint for default partition would be violated by some row', or when the default partition is found to be growing.

Caution

With a default partition present, every new partition scans the default in full and holds ACCESS EXCLUSIVE on it meanwhile; a large default partition means an outage at every maintenance. The default is closed to writes while rows are moved; do it during low traffic. Permanent fix: pre-create partitions and keep the default empty with monitoring (alert on row count > 0), or do not use one at all, so out-of-range INSERTs fail and the problem surfaces early. A default partition also prevents DETACH ... CONCURRENTLY.