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.
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).