A PostgreSQL transaction can temporarily contain rows that violate a unique constraint and still remain executable. That state is possible only when the constraint is declared deferrable and its current mode is deferred. The duplicate is not accepted as valid data; enforcement has moved from the statement boundary to a later constraint-check boundary.
This timing distinction changes which multi-statement transformations are representable. It also changes where an error can surface, which statements can act as conflict arbiters, and what application code can safely infer from the success of an individual write.
Immediate uniqueness rejects an invalid statement result
A normal UNIQUE constraint is not deferrable unless declared otherwise. PostgreSQL checks a non-deferrable constraint after each command.
Consider a position column whose values must be unique:
CREATE TABLE queue (
item_id bigint PRIMARY KEY,
position integer UNIQUE
);If two rows hold positions 1 and 2, a sequence that first changes position 1 to 2 creates a duplicate at the end of that statement. A later statement intended to move the other row cannot repair the state because execution has already crossed an immediate constraint boundary.
The relevant unit is the result of each statement, not the application’s planned final sequence. A transaction does not automatically grant permission to pass through constraint-invalid intermediate states.
This property is useful when every successful statement must leave uniqueness intact. It can also make some reorderings require a spare value, a single statement with suitable semantics, or a different constraint timing model.
Deferral separates mutation time from validation time
A unique constraint can instead be declared with deferred capability:
CREATE TABLE queue (
item_id bigint PRIMARY KEY,
position integer,
CONSTRAINT queue_position_key
UNIQUE (position)
DEFERRABLE INITIALLY DEFERRED
);With this declaration, the constraint begins each transaction in deferred mode. PostgreSQL permits affected statements to proceed without requiring the constraint to be satisfied at each statement boundary. The constraint must still be satisfied when its deferred check occurs, normally at transaction commit unless its mode is changed earlier.
That makes a temporary collision possible:
BEGIN;
UPDATE queue SET position = 2 WHERE item_id = 10;
UPDATE queue SET position = 1 WHERE item_id = 20;
COMMIT;Assume item 10 started at position 1, item 20 at position 2, and no other row uses either value. After the first update, two rows temporarily carry position 2. After the second, the final values are unique again. A deferred constraint can admit that intermediate state and accept the transaction once the final check succeeds.
The same transaction would fail if the duplicate remained at the check point. Deferral changes enforcement timing, not the uniqueness rule.
INITIALLY IMMEDIATE preserves an explicit timing choice
DEFERRABLE and INITIALLY DEFERRED describe different properties. The first permits the check time to move. The second selects the starting mode for each transaction.
A constraint can be declared:
UNIQUE (position) DEFERRABLE INITIALLY IMMEDIATESuch a constraint starts in immediate mode, so ordinary statements encounter uniqueness checks at statement boundaries. A transaction that needs a deferred interval can change the mode:
SET CONSTRAINTS queue_position_key DEFERRED;This arrangement keeps immediate checking as the default while allowing selected transactions to opt into a later boundary.
A non-deferrable unique constraint cannot be converted into a deferred one with SET CONSTRAINTS. Deferrability is part of the constraint definition, while the current immediate or deferred mode is transaction state for constraints that permit mode changes.
Switching back to immediate mode performs a retroactive check
Moving a constraint from deferred to immediate mode is not merely a setting for future writes. PostgreSQL checks outstanding changes when SET CONSTRAINTS ... IMMEDIATE executes.
A transaction can therefore create a temporary conflict and request an explicit validation point:
BEGIN;
SET CONSTRAINTS queue_position_key DEFERRED;
UPDATE queue SET position = 2 WHERE item_id = 10;
SET CONSTRAINTS queue_position_key IMMEDIATE;Given the earlier two-row state, the final command fails because position 2 is duplicated at that moment. If preceding statements had repaired the collision first, the mode change could succeed and subsequent statements would operate under immediate checking.
This behavior gives the transaction an internal validation boundary before commit. Application code can place that boundary before later work that should execute only after the deferred constraints have been verified.
A failed mode change also means that success of earlier deferred writes cannot be treated as proof that their resulting state satisfies the constraint.
Deferrable uniqueness changes ON CONFLICT compatibility
PostgreSQL’s INSERT ... ON CONFLICT machinery needs an arbiter that can decide the relevant conflict as part of the statement. Deferrable constraints do not provide that role for ON CONFLICT.
This follows from the timing contract. A deferred uniqueness rule can permit a conflict to exist until a later check point, while ON CONFLICT needs conflict identification during execution of the insert statement in order to select its alternate action.
A schema choice made for multi-statement constraint timing can therefore affect write APIs elsewhere. Replacing a non-deferrable unique constraint with a deferrable one is not purely an enforcement-delay change when application statements depend on that constraint as an ON CONFLICT arbiter.
The constraint definition and the mutation syntax need compatible timing semantics.
Deferred validation does not imply isolation from concurrent transactions
Constraint deferral controls when PostgreSQL validates the constraint for a transaction. It does not grant a private duplicate-key namespace or make concurrent transactions irrelevant.
Concurrent inserts or updates can interact through the indexes and transaction state used to enforce uniqueness. Depending on the operations and timing, a transaction can wait for another transaction, encounter an error at a constraint check, or proceed after the other transaction’s outcome makes the key available.
The exact schedule depends on the statements, isolation state, lock and index interactions, and transaction outcomes. Deferral alone does not establish a general non-blocking guarantee.
The stable semantic claim is narrower: a deferrable unique constraint in deferred mode permits its own validity check to occur later than the statement that produced the intermediate state. Concurrency remains subject to PostgreSQL’s normal transactional machinery.
The final state remains the invariant
Deferred constraints are sometimes described as weaker constraints because invalid intermediate states can exist. The database invariant is not weakened at the selected enforcement boundary. A transaction cannot commit successfully while a deferred unique constraint remains violated.
This creates two distinct notions of validity during execution:
statement completed successfully
constraint currently satisfiedFor an immediate constraint, successful completion normally joins those facts at the statement boundary. For a deferred constraint, they can diverge until the next check point.
That distinction matters for code that performs side effects outside the database before commit. A successful SQL statement under deferred checking does not establish that the transaction will later pass uniqueness validation. External work that assumes eventual commit can therefore run ahead of a database error unless the application coordinates its effect boundary with transaction outcome.
The same issue exists for other commit-time failures, so deferred uniqueness is one concrete instance of a broader transaction rule: statement success is not equivalent to successful commit.
Constraint timing is part of schema semantics
A unique constraint specifies more than the set of legal committed key combinations. In PostgreSQL, its deferrability also determines the legal path a transaction can take toward that state.
Non-deferrable uniqueness requires each statement result to satisfy the rule. Deferrable immediate uniqueness keeps that default but permits a transaction to move the boundary. Deferred uniqueness permits temporary conflicts and validates them later.
Those modes support different mutation shapes and expose errors at different points. They also interact differently with statement features that require immediate arbitration.
The practical boundary is precise: deferral expands the set of intermediate transaction states PostgreSQL can represent without expanding the set of constraint-violating states that may successfully pass the selected final check.