On Wed, Feb 9, 2022 at 2:24 PM Thomas Kellerer <shammat@gmx.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."
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."
