Blogmark

Nice example of when deferrable constraints matter in PostgreSQL.

CREATE TABLE tab (id numeric PRIMARY KEY);

INSERT INTO tab (id) VALUES (1);
INSERT INTO tab (id) VALUES (2);

UPDATE tab SET id = id + 1;
ERROR:  duplicate key value violates unique constraint "tab_pkey"
DETAIL:  Key (id)=(2) already exists.

The reason is that PostgreSQL (in violation of the SQL standard) checks the constraint after each row, while Oracle checks it at the end of the statement. To make PostgreSQL behave the same as Oracle, create the constraint as DEFERRABLE. Then PostgreSQL will check it at the end of the statement.