Thanks! Perhaps this behavior should also be explained elsewhere. It didn't occur to me to look in the "Compatibility" section.
On Wed, Jul 14, 2021 at 3:33 AM Pantelis Theodosiou <ypercube@gmail.com> wrote:
UNIQUE constraints have this behaviour. It is explained in the section:
Non-Deferred Uniqueness Constraints
When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values. To obtain standard-compliant behavior, declare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be significantly slower than immediate uniqueness checking.
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
