https://www.postgresql.org/docs/12/sql-createtable.html
[quote]
DEFERRABLE
NOT DEFERRABLE
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 [/quote]
But yet a
DEFERRABLE
FK constraint in a transaction immediately failed on a FK constraint
violation.[quote]
INITIALLY IMMEDIATE
INITIALLY DEFERRED
If a constraint is deferrable, this clause
specifies the default time to check the constraint. If the
constraint is INITIALLY IMMEDIATE
, it is checked after each
statement. This is the default. If the constraint is INITIALLY DEFERRED
, it is checked only
at the end of the transaction.
INITIALLY DEFERRED solved my problem. Why do both clauses exist?
(A naive interpretation just by looking at the clause words led me to think that INITIALLY DEFERRED would not check record validity when a constraint is added to a table, but obviously that's wrong too.)
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.