1

I have 2 simple tables with a foreign key constraint:

CREATE TABLE a(i integer);
ALTER TABLE a ADD CONSTRAINT pkey_a PRIMARY KEY (i);
CREATE TABLE b(j integer);
ALTER TABLE b add CONSTRAINT fkey_ij FOREIGN KEY (j) REFERENCES a (i);

I want to defer the constraint for the duration of a transaction

START TRANSACTION;
SET CONSTRAINTS fkey_ij DEFERRED;

I get the following error:

[42809] ERROR: constraint "fkey_ij" is not deferrable

I'm reading the postgres SET CONSTRAINTS docs and don't understand why this constraint would not be eligible to be deferred:

Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). Uniqueness and exclusion constraints that have not been declared DEFERRABLE are also checked immediately.

Am I missing something from the docs?

Nicolas C
  • 944
  • 1
  • 10
  • 22

1 Answers1

4

A) The doc link is for version 9.1 which is well past EOL(~5.25 yrs).

B) Yes, you missed the next paragraph:

Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE. .

So the constraint needs to be created as DEFERRABLE or altered to it. See ALTER TABLE:

ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • Thanks. For some reason, I thought that `DEFERRABLE INITIALLY IMMEDIATE` was the default. The doc clearly states "NOT DEFERRABLE is the default" https://www.postgresql.org/docs/14/sql-createtable.html – Nicolas C Feb 18 '22 at 20:12