A uniqueness rule can be valid for a transaction even when an intermediate statement temporarily creates duplicate keys. PostgreSQL supports that distinction through deferrable unique constraints: enforcement can move from each modifying statement to a later constraint-check point.
This behavior changes transaction semantics rather than removing the rule. Duplicate values may exist transiently in transaction-local work, but a deferred constraint still has to be satisfied before the transaction can commit successfully.
Immediate and deferred modes define the check point
A unique constraint declared without DEFERRABLE is checked in the normal immediate mode and cannot be switched to deferred mode later. A constraint declared DEFERRABLE can participate in transaction-level timing control.
INITIALLY IMMEDIATE makes a deferrable constraint start each transaction in immediate mode. INITIALLY DEFERRED starts it in deferred mode. The transaction can change the active mode with SET CONSTRAINTS for constraints that were declared deferrable.
The distinction is visible when several writes form one logical rearrangement. An intermediate state that violates uniqueness can be accepted while the constraint is deferred, provided later writes restore a valid state before the constraint is checked.
Temporary duplicates can support key rearrangement
Consider a table whose position value must remain unique. Swapping two occupied positions can pass through an intermediate duplicate if the changes are expressed as separate operations.
With immediate enforcement, the first conflicting write can fail before the second write has a chance to complete the rearrangement. A deferred constraint allows the transaction to perform the related changes and present a unique final state at its check point.
This does not make arbitrary duplicates safe. If the transaction reaches the deferred check with duplicate constrained values still present, PostgreSQL reports a constraint violation and the transaction cannot complete normally.
The useful boundary is therefore temporal: the database accepts an invalid intermediate arrangement only inside the transaction and only until the configured check point.
SET CONSTRAINTS can bring validation forward
SET CONSTRAINTS ... DEFERRED postpones checking for named deferrable constraints, or for all applicable constraints when ALL is used. SET CONSTRAINTS ... IMMEDIATE moves them back to immediate checking.
Changing a constraint from deferred to immediate has an important effect: PostgreSQL checks outstanding changes when that command runs. If the current transaction state violates the constraint, the mode change fails rather than silently carrying the violation forward.
That property makes the command more than a setting for future statements. It can establish an explicit validation boundary inside a larger transaction.
Constraint names also matter operationally. PostgreSQL allows constraint names that are not globally unique across every schema object, so broad or ambiguous naming can make targeted mode changes less obvious than schema designs with distinctive constraint names.
Deferrability is part of the constraint definition
Deferral is not a session switch that can be applied to any uniqueness rule. The constraint must have been created as deferrable.
A table definition can express the timing directly:
CREATE TABLE queue_item (
id bigint PRIMARY KEY,
position integer,
CONSTRAINT queue_item_position_key
UNIQUE (position)
DEFERRABLE INITIALLY DEFERRED
);Here, uniqueness still belongs to the schema. The declaration changes the default timing of its enforcement within each transaction.
This is materially different from dropping the constraint during a batch operation or moving validation entirely into application code. The database retains responsibility for rejecting a transaction whose checked state violates the declared rule.
Deferrable uniqueness is not interchangeable with every unique index use
PostgreSQL represents unique constraints with supporting indexes, but SQL features that require an immediate conflict arbiter do not treat all uniqueness definitions identically.
In particular, INSERT ... ON CONFLICT uses eligible unique indexes or constraints to identify conflicts during the statement. Deferrable constraints cannot serve as ON CONFLICT arbiters. Conflict-handling syntax needs a uniqueness mechanism whose decision is available at statement execution rather than one whose violation may remain pending until a later check point.
This creates a schema design boundary. A constraint selected for transaction-level deferral is suited to operations that need temporary invalid intermediate states. A uniqueness rule intended to drive statement-level upsert conflict handling needs immediate arbitration.
Deferred checking changes failure timing
Immediate constraints localize many errors near the statement that introduced them. Deferred constraints can move the visible failure later, potentially as far as transaction commit.
That timing affects application control flow. A sequence of statements can all execute without a uniqueness error and still fail when PostgreSQL performs the deferred check. Code that treats successful execution of the final data-changing statement as proof that the transaction will commit has an incomplete model when deferred constraints are present.
Explicitly switching selected constraints to immediate mode before later work can create an earlier failure boundary when the application needs one.
The database still preserves atomicity: if the deferred rule is not satisfied, the transaction does not commit a final state that violates the constraint.
The mechanism fits invariants that apply to completed rearrangements
Deferrable uniqueness is most useful when the invariant belongs to the completed transaction state but legitimate operations can temporarily cross an invalid intermediate state. Ordered positions, mutually exchanged identifiers, and coordinated key reassignment can fit that shape.
Using deferral merely to postpone ordinary duplicate-key errors offers less value and makes error timing less local. The feature is strongest when transaction structure itself requires a temporary conflict and the schema can still state the final invariant precisely.
The central design choice is not whether uniqueness matters. It is the point in the transaction at which PostgreSQL must require that uniqueness to hold.