When is the NOT NULL constraint executed and can it wait for the transaction to complete?

Question

I have a table populated during an ETL procedure column by column at a time. Mandatory columns (which are foreign keys) are set first and immediately, so the initial state of the table is:

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

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

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, apparently, he tried to insert a value fkeylike null.

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

Is the syntax correct? Why does this fail? Does SQLAlchemy use any part in this error, or does it execute PLSQL correctly?

, CONFLICT, , , fkey , .

Answer 1

PostgreSQL, , .

UNIQUE, PRIMARY KEY, REFERENCES ( ) EXCLUDE. NOT NULL CHECK , ​​ ( ). , DEFERRABLE.

NOT NULL, , , , .

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