Under the hood: when a constraint is checked

Step 1 of 110%

Concept · step 1 of 11

Now, or at COMMIT

Every constraint so far said no the moment a bad row appeared. Some can wait instead: a DEFERRABLE constraint may be checked later, as long as the rule holds by the time it is checked.

Always at once. NOT NULL and CHECK look at each row as it is written, and cannot be told to wait.
After the statement. a DEFERRABLE constraint is, by default, checked once the whole statement has run. This is INITIALLY IMMEDIATE.
At COMMIT. an INITIALLY DEFERRED constraint waits until the transaction ends, so the rows may break it in between.

CREATE TABLE steps (
  id       INT PRIMARY KEY,
  position INT NOT NULL UNIQUE DEFERRABLE
);

-- inside one transaction: wait until COMMIT
SET CONSTRAINTS ALL DEFERRED;

Only four kinds can wait: UNIQUE, PRIMARY KEY, REFERENCES and EXCLUDE. The board is a recipe: two steps, numbered by position. Reordering them is the most ordinary thing an app does with a list, and it is where most people first meet this.

PostgreSQL docs: CREATE TABLE, DEFERRABLE and INITIALLY DEFERRED ↗

Starting Postgres in your browser

About 5 MB the first time, cached after that.

PostgreSQL running in your browser