Most PostgreSQL constraints reject invalid state as soon as the relevant statement is checked. That timing is usually desirable, but some valid multi-statement changes pass through a temporary state that violates a uniqueness, foreign-key, primary-key, or exclusion rule.

A deferrable constraint changes the timing rather than the rule itself. PostgreSQL can postpone its check until transaction commit, allowing intermediate row states that would fail under immediate checking. The final transaction state must still satisfy the constraint.

Deferrability is a property of the constraint

DEFERRABLE does not mean that a constraint is deferred at all times. It means its check mode can be changed within a transaction. The initial mode is controlled separately:

CREATE TABLE task (
    task_id bigint PRIMARY KEY,
    position integer NOT NULL,
    CONSTRAINT task_position_key
        UNIQUE (position)
        DEFERRABLE INITIALLY IMMEDIATE
);

Here, task_position_key starts each transaction in immediate mode. It can be switched to deferred mode when a transaction needs to make several coordinated changes.

INITIALLY DEFERRED changes that default. A constraint declared with both DEFERRABLE and INITIALLY DEFERRED starts each transaction deferred unless its mode is changed.

PostgreSQL accepts deferrability for UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES constraints. NOT NULL and CHECK constraints do not support deferred checking. This boundary matters when a data model depends on temporary intermediate states: not every integrity rule can be moved to commit time.

Temporary uniqueness conflicts can disappear before commit

Consider two rows whose positions need to be exchanged:

task_id | position
--------+---------
10      | 1
20      | 2

With a normal non-deferrable unique constraint on position, an update that temporarily gives both rows the same value can fail before the transaction reaches its intended final state.

A deferrable unique constraint permits a transaction to postpone that check:

BEGIN;

SET CONSTRAINTS task_position_key DEFERRED;

UPDATE task SET position = 2 WHERE task_id = 10;
UPDATE task SET position = 1 WHERE task_id = 20;

COMMIT;

After the first UPDATE, the transaction contains a temporary duplicate position. The deferred constraint allows execution to continue. After the second UPDATE, uniqueness has been restored, so the constraint can succeed when checked at commit.

The same timing model can matter for coordinated foreign-key changes or exclusion constraints. Deferral is useful only when the intermediate violation is temporary and the transaction can establish a valid final state.

SET CONSTRAINTS changes transaction-local timing

SET CONSTRAINTS operates within the current transaction. It can target named deferrable constraints or all deferrable constraints:

SET CONSTRAINTS task_position_key DEFERRED;
SET CONSTRAINTS ALL IMMEDIATE;

Changing a deferred constraint to IMMEDIATE is not merely a setting for later statements. PostgreSQL checks outstanding changes at that point. If the current transaction state violates the constraint, the SET CONSTRAINTS statement fails.

That behavior makes an explicit switch back to immediate mode a useful validation boundary inside a long transaction. It can force pending integrity checks before later work proceeds.

Constraint names also deserve care. PostgreSQL requires constraint names to be unique per table, not across an entire schema. A schema-qualified name can narrow lookup to a schema, but multiple constraints with the same name in that schema can still be affected.

Deferred checking changes failure timing

Moving a check to commit time changes where an error can surface. Application code that assumes every integrity violation appears directly after the modifying statement can be surprised by a commit failure.

That is not a weaker integrity guarantee. A transaction with an unresolved deferred violation cannot commit successfully. The difference is that several statements are evaluated as a coordinated unit before the deferred rule is enforced.

This timing also affects transaction design. If a transaction performs unrelated work after creating a temporary violation, all of that work remains subject to rollback if the deferred check later fails. Keeping the mutation set focused makes the eventual integrity boundary easier to reason about.

Deferrable uniqueness has an ON CONFLICT boundary

A deferrable constraint is not interchangeable with every non-deferrable constraint. PostgreSQL does not allow deferrable constraints to act as conflict arbiters for INSERT ... ON CONFLICT.

That restriction matters when a unique key serves two roles: enforcing uniqueness and selecting the conflict target for an upsert. Making the constraint deferrable to support multi-statement reordering can make it unsuitable for the second role.

Schema design therefore needs to account for check timing and statement semantics together. Deferral solves a specific transactional problem; it is not a general replacement for immediate constraints.

The final state remains the contract

Deferrable constraints are most useful when validity depends on the transaction as a whole rather than every intermediate statement. They let PostgreSQL tolerate a temporary inconsistency without accepting it as committed data.

The key boundary is explicit: only supported constraint types can be deferred, the mode applies within a transaction, and unresolved violations still prevent commit. When an operation can be expressed without temporary invalid state, immediate checking keeps failures closer to the statement that caused them. Deferral is better reserved for mutations whose valid final state genuinely requires intermediate rearrangement.

PostgreSQL Database Constraints Transactions