skip to content

In PostgreSQL, which constraints can be declared DEFERRABLE, and what do you give up by making a UNIQUE or EXCLUDE constraint deferrable?

level: seniorimportance: nice to knowfreq 20%

answer

  1. four kinds qualify
  2. per row versus statement end
  3. arbiters must be immediate
  4. foreign key targets too
  5. ALTER CONSTRAINT is FK-only

basics

~20 s

Only UNIQUE, PRIMARY KEY, EXCLUDE and FOREIGN KEY constraints can be DEFERRABLE. A deferrable UNIQUE or EXCLUDE cannot arbitrate ON CONFLICT, a deferrable unique key cannot be a foreign key target, checking gets slower, and errors surface later.

solid answer

~50 s

PostgreSQL accepts `DEFERRABLE` on `UNIQUE`, `PRIMARY KEY`, `EXCLUDE` and `FOREIGN KEY`; `CHECK` and `NOT NULL` are always checked immediately, row by row. A **non-deferrable** `UNIQUE`, `PRIMARY KEY` or `EXCLUDE` is also checked as each row is written, which is stricter than the standard's end-of-statement rule; `DEFERRABLE INITIALLY IMMEDIATE` gives the standard behaviour, and `INITIALLY DEFERRED` or `SET CONSTRAINTS` moves the check to `COMMIT`. The price: no `INSERT ... ON CONFLICT` form accepts a deferrable arbiter; a foreign key can only reference a non-deferrable unique or primary key; the planner will not use a deferrable unique index to prove uniqueness, for example to remove a join; checking is slower, because conflicts are recorded and rechecked; and errors arrive at statement end or `COMMIT`. In 18, `ALTER CONSTRAINT` changes deferrability only for foreign keys, so the rest must be dropped and recreated.

code

sql · 14 lines
sql
ALTER TABLE booking DROP CONSTRAINT booking_no_overlap;
ALTER TABLE booking
    ADD CONSTRAINT booking_no_overlap
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
    WHERE (cancelled_at IS NULL)
    DEFERRABLE INITIALLY IMMEDIATE;

-- swap two adjacent slots of room 7 in one statement
UPDATE booking
SET during = CASE booking_id
                 WHEN 101 THEN tstzrange('2026-10-06 10:00+00', '2026-10-06 11:00+00')
                 WHEN 102 THEN tstzrange('2026-10-06 09:00+00', '2026-10-06 10:00+00')
             END
WHERE booking_id IN (101, 102);

go deeper

for a junior

Recall the four kinds that accept DEFERRABLE (UNIQUE, PRIMARY KEY, EXCLUDE, FOREIGN KEY) and that CHECK and NOT NULL are always checked at once.

for a middle

Explain PostgreSQL's per-row check of non-deferrable unique and exclusion constraints, and how INITIALLY IMMEDIATE and INITIALLY DEFERRED move the check.

for a senior

Show the production costs: upserts and foreign keys that stop working, lost join removal, slower checks, errors at COMMIT, and drop-and-recreate because ALTER CONSTRAINT is FK-only.

for a principal

Set a schema convention for which constraints may ever be deferrable, how that interacts with upsert-heavy write paths, and how it is reviewed before a constraint is created.

## Which constraints accept DEFERRABLE | Constraint | `DEFERRABLE` allowed | When a non-deferrable one is checked | |---|---|---| | `NOT NULL` | no | immediately, per row | | `CHECK` | no | immediately, per row | | `UNIQUE`, `PRIMARY KEY` | yes | immediately, per row | | `EXCLUDE` | yes | immediately, per row | | `FOREIGN KEY` | yes | after the statement finishes | Writing `CHECK (...) DEFERRABLE` fails with "CHECK constraints cannot be marked DEFERRABLE". The declared behaviour is recorded in `pg_constraint.condeferrable` and `condeferred`. (What deferral is for, and when to reach for it in general, is the relational-constraints topic; this answer is about PostgreSQL's rules and costs.) ## PostgreSQL's per-row uniqueness check The SQL standard says uniqueness is checked at the end of the statement. PostgreSQL checks **non-deferrable** unique, primary-key and exclusion constraints **as each row is written**. That matters when one statement moves several keys. Swapping the time slots of two bookings for the same room in one `UPDATE` can fail with `23P01`: after the first row takes the second row's slot, the second row's old version still occupies it. Declaring the constraint `DEFERRABLE` changes the mechanism: - for a deferrable unique index, the insertion notes a possible duplicate instead of raising an error; - for a deferrable exclusion constraint, the conflict scan still runs straight away, but a conflict is only remembered; - a queued recheck then runs at the end of the statement (`INITIALLY IMMEDIATE`, the standard's timing) or at `COMMIT` (`INITIALLY DEFERRED`, or `SET CONSTRAINTS name DEFERRED` inside a transaction). So the swap above succeeds with `DEFERRABLE INITIALLY IMMEDIATE`, because by the end of the statement both rows have moved. ## What a deferrable UNIQUE or EXCLUDE costs 1. **No upsert arbiter.** `INSERT ... ON CONFLICT` accepts only `NOT DEFERRABLE` constraints and unique indexes as arbiters. Existing upserts fail with "ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters", including a bare `DO NOTHING` on a table that has such a constraint. 2. **No foreign key target.** A foreign key's referenced columns must be a non-deferrable `UNIQUE` or `PRIMARY KEY` constraint, or a non-partial unique index. A deferrable primary key cannot be referenced. 3. **Weaker planning.** The planner proves a relation's uniqueness only from unique indexes that are immediately enforced and not partial. A deferrable one does not count, so optimisations such as removing a provably redundant left join are lost. 4. **Slower checks.** The manual warns that deferrable-but-not-deferred uniqueness checking "can be significantly slower" than immediate checking, because of the recording and the recheck. 5. **Later, harder-to-place errors.** A violation appears at statement end or at `COMMIT`, possibly far from the statement that caused it, so error handling must expect `23505` or `23P01` from `COMMIT` itself. ## Changing your mind later In PostgreSQL 18, `ALTER TABLE ... ALTER CONSTRAINT ... DEFERRABLE` works only on foreign keys; for anything else it fails with "constraint ... is not a foreign key constraint". Making a booking `EXCLUDE` constraint deferrable therefore means `DROP CONSTRAINT` and `ADD CONSTRAINT ... DEFERRABLE`, which rebuilds the GiST index inside `ALTER TABLE` under an `ACCESS EXCLUSIVE` lock, and leaves the table unprotected if the two steps run in separate transactions. Decide deferrability when you create the constraint. ## Inspecting what you have ```sql SELECT conrelid::regclass AS table_name, conname, contype, condeferrable, condeferred FROM pg_constraint WHERE condeferrable ORDER BY 1, 2; ``` Every `u`, `p` or `x` row in the result is a constraint that cannot serve as an `ON CONFLICT` arbiter, and every `u` or `p` row is a key no foreign key may reference ("cannot use a deferrable primary key for referenced table"). Run the query before a migration that adds an upsert or a new reference to one of those tables. ## Practical guidance - Keep unique and exclusion constraints `NOT DEFERRABLE` by default, especially those that serve as upsert targets or foreign key targets. - Make one deferrable only when a real write path moves keys as a set, such as slot swaps or reordering, and the costs above are acceptable for that table. - Prefer deferring foreign keys: they are never upsert arbiters or foreign key targets, `ALTER CONSTRAINT` can switch them both ways, and they cover the common case of inserting rows in an inconvenient order.

  • In PostgreSQL, why can one UPDATE that swaps two bookings' slots fail under a non-deferrable EXCLUDE constraint?
    PostgreSQL checks a non-deferrable exclusion constraint as each new row version is written. When the first row takes the second row's slot, the second row's old version still holds that slot, so the check finds an overlap and raises `23P01` before the second row is updated. `DEFERRABLE INITIALLY IMMEDIATE` moves the recheck to the end of the statement, when both rows have moved.
  • In PostgreSQL 18, can you make an existing booking EXCLUDE constraint deferrable without rebuilding it?
    No. `ALTER CONSTRAINT` changes deferrability only for foreign keys and otherwise fails with "is not a foreign key constraint". You drop the exclusion constraint and add it again with `DEFERRABLE`, which rebuilds its GiST index under `ACCESS EXCLUSIVE`; do both in one transaction so overlapping bookings cannot slip in between.
  • Why might PostgreSQL upserts start failing after a migration made a UNIQUE constraint DEFERRABLE?
    `INSERT ... ON CONFLICT` requires `NOT DEFERRABLE` arbiters. Once the constraint is deferrable, statements whose conflict target matches it, and bare `ON CONFLICT DO NOTHING` on that table, fail with "ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters". Either keep the constraint immediate or rewrite those writes; arbiter inference itself is the upsert topic.

  • CHECK constraints can be made DEFERRABLE so they are validated at commit.
  • PostgreSQL checks non-deferrable UNIQUE constraints at the end of each statement.
  • A deferrable UNIQUE constraint still works as an ON CONFLICT arbiter.
  • Any constraint's deferrability can be changed in place with ALTER CONSTRAINT.
  • DEFERRABLE INITIALLY IMMEDIATE behaves exactly like NOT DEFERRABLE in PostgreSQL.