PostgreSQL Constraint Checking Behavior (Deferred or Immediate)

What it is?

Constraint is a way in the database to keep the integrity of data. It would check every data creation made. In transaction, the constraints would have different behavior type:

• IMMEDIATE constraints are checked at the end of each statement.
• DEFERRED constraints are not checked until the transaction is committed.

Constraints Characteristics

Upon creation, a constraint is given one of three characteristics:

• DEFERRABLE INITIALLY DEFERRED
• DEFERRABLE INITIALLY IMMEDIATE
• NOT DEFERRABLE (default), always IMMEDIATE and is not affected by the SET CONSTRAINTS command

The first two classes start every transaction in the indicated mode, but their behavior can be changed within a transaction by SET CONSTRAINTS.

How to SET constraint behavior

By default, the constraint is NOT DEFERRABLE means we should define the constraint to be DEFERRABLE first before making it editable.

ALTER TABLE <table_name>
  ADD CONSTRAINT <constraint_name>
  UNIQUE (first_name, last_name, email)
  DEFERRABLE;

or we can alter existing constraint

ALTER TABLE <table_name>
  ALTER CONSTRAINT <constraint_name>
  DEFERRABLE;