The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/13/sql-createtable.html
Description:
According to the docs:
A constraint that is not deferrable will be checked immediately after every
command.
But this is the behavior I observe on PG 13.3:
create table t (n int primary key);
insert into t values (1), (2), (3);
update t set n = n + 1;
ERROR: 23505: duplicate key value violates unique constraint "t_pkey"
DETAIL: Key (n)=(2) already exists.
If the constraint was checked *after* the command it should work. It appears
it is checked before the command has finished.
In contrast a DEFERRABLE INITIALLY IMMEDIATE constraint which is documented
as "If the constraint is INITIALLY IMMEDIATE, it is checked after each
statement." behaves as expected.
create table t (n int primary key deferrable initially immediate);
insert into t values (1), (2), (3);
update t set n = n + 1; --> UPDATE 3