NOT DEFERRABLE versus DEFERRABLE INITIALLY IMMEDIATE

Aside from the other (correct) answers, when speaking of PostgreSQL, it must be stated that:

• with NOT DEFERRABLE each row is checked at insert/update time

• with DEFERRABLE (currently IMMEDIATE) all rows are checked at the end of the insert/update

• with DEFERRABLE (currently DEFERRED) all rows are checked at the end of the transaction

So it's not correct to say that a DEFERRABLE constraint set to IMMEDIATE acts like a NOT DEFERRABLE one.

---

Let's elaborate on this difference:

CREATE TABLE example(
    row integer NOT NULL,
    col integer NOT NULL,
    UNIQUE (row, col) DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO example (row, col) VALUES (1,1),(2,2),(3,3);

UPDATE example SET row = row + 1, col = col + 1;

SELECT * FROM example;

This correctly outputs:

But if we remove the DEFERRABLE INITIALLY IMMEDIATE instruction,

ERROR: duplicate key value violates unique constraint "example_row_col_key" DETAIL: Key ("row", col)=(2, 2) already exists. ********** Error **********

ERROR: duplicate key value violates unique constraint "example_row_col_key" SQL state: 23505 Detail: Key ("row", col)=(2, 2) already exists.

---

ADDENDUM (October 12, 2017)

This behavior is indeed documented here, section "Compatibility":

Also, PostgreSQL checks non-deferrable uniqueness constraints immediately, not at end of statement as the standard would suggest.

With DEFERRABLE INITIALLY IMMEDIATE you can defer the constraints on demand when you need it.

This is useful if you normally want to check the constraints at statement time, but for e.g. a batch load want to defer the checking until commit time.

The syntax how to defer the constraints is different for the various DBMS though.

With NOT DEFERRABLE you will never ever be able to defer the checking until commit time.