Re: DELETING then INSERTING record with same PK in the same TRANSACTION

On Wed, Feb 9, 2022 at 2:24 PM Thomas Kellerer <shammat(at)gmx(dot)net> wrote:

> David G. Johnston schrieb am 09.02.2022 um 21:47:
> > You cannot defer uniqueness checks to transaction commit so either it
> > is going to fail on the insert or it will not fail at all.
>
> You can defer unique constraints, but not primary key constraints.
>
>
Actually we are both wrong...

"This controls whether the constraint can be deferred. A constraint that is
not deferrable will be checked immediately after every command. Checking of
constraints that are deferrable can be postponed until the end of the
transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the
default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES
(foreign key) constraints accept this clause."

https://www.postgresql.org/docs/current/sql-createtable.html

In any case I would not expect that primary key constraints would behave
differently than any other unique constraint. The null-ness check for a PK
cannot be deferred, however.

"NOT NULL and CHECK constraints are not deferrable. Note that deferrable
constraints cannot be used as conflict arbitrators in an INSERT statement
that includes an ON CONFLICT DO UPDATE clause."

David J.