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 <yperc...@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. > > On Wed, Jul 14, 2021 at 9:29 AM PG Doc comments form < > nore...@postgresql.org> wrote: > >> 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 >> >
