When does a NOT NULL constraint run and can it wait until the transaction is going to commit?

Question

I have a table being populated during an ETL routine a column at a time. The mandatory columns (which are foreign keys) are set first and at once, so the initial state of the table is:

key    | fkey   | a
-------|--------|-------
1      | 1      | null

After processing A values, I insert them using SQL Alchemy with PostgreSQL dialect for a simple upsert:

upsert = sqlalchemy.sql.text("""
    INSERT INTO table
      (key, a)
    VALUES (:key, :a)
    ON CONFLICT (key) DO UPDATE SET
      a = EXCLUDED.a
""")

But this fails because it apparently tried to insert the fkey value as null.

psycopg2.IntegrityError: null value in column "fkey" violates not-null constraint
DETAIL:  Failing row contains (1, null, 0).

Is the syntax really correct? Why is it failing? Does SQLAlchemy has any participation on this error or is it translating PLSQL correctly?

My suspicion is that the constraint checks happen before the CONFLICT resolution triggers, so although it would actually work because fkey is guaranteed to be not null before and won't be overwritten, the constraint check only looks at the tentative insertion and the table constraints.

Maggi

PS: I know I can use an UPDATE instead of INSERT+ON CONFLICT, since the row is guaranteed to be there. I'm reusing old code and just want to know why it is failing. – Maggi

You are correct, postgres evaluates constraints on UPSERT even if it will always go to the ON CONFLICT part. You can work around this in many ways, like using UPDATE you mentioned, or switching from constraint to trigger AFTER event, as it won't fire for rows that have ON CONFLICT part executed. – Evincive

Answer 1

This is a current documented limitation of PostgreSQL, an area where it breaks the spec.

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.

You can't defer the NOT NULL constraint, and it seems you understand the default behavior, seen here.

CREATE TABLE foo ( a int NOT NULL, b int UNIQUE, c int );
INSERT INTO foo (a,b,c) VALUES (1,2,3);

INSERT INTO foo (b,c) VALUES (2,3);
ERROR:  null value in column "a" violates not-null constraint
DETAIL:  Failing row contains (null, 2, 3).

Mouthy