NOT NULL constraint violation at UPDATE ON CONFLICT query

Question

I have a table player with the following DDL. I only changed the column names and the order:

create table if not exists player (
    id varchar primary key,
    col1 boolean not null default false,
    col2 json not null default '{}',
    col3 varchar not null,
    col4 varchar not null,
    col5 json not null default '{}',
    col6 boolean not null default false
);

Here's the query I tried to run:

insert into player(id, col1, col2)
values (val_id, val1, val2)
on conflict(id)
do update set col1=excluded.col1, col2=excluded.col2

(Another row with the same id exists, so UPDATE should happen.)

col3 has a NOT NULL constraint. (I verified that it has a value before the query.) Yet, when the query runs, Postgres tells me:

ERROR: ... null value in column "col3" of relation "player" violates not-null constraint

When I gave col3 a default value, no error occurred and all the values were as they should be. But I don't need a default value.

I checked the Postgres docs but cannot find what is wrong with the query. What could be the problem?

Answer 1

TL;DR

If col3 is defined NOT NULL without default, the INSERT must provide a non-null value for it.

In depth

NOT NULL constraints are checked first. The manual:

When a table has multiple CHECK constraints, they will be tested for each row in alphabetical order by name, after checking NOT NULL constraints.

Bold emphasis mine.
You might try to replace NOT NULL with a plain CHECK constraint. The manual:

A not-null constraint is always written as a column constraint. A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient. The drawback is that you cannot give explicit names to not-null constraints created this way.

But to no avail. Either constraint is checked immediately, and cannot be deferred. The manual:

NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement).

Bold emphasis mine.
This is the core issue.

Do one of these to resolve:

1. Define a non-null column default for col3.
   (Possibly add a BEFORE INSERT trigger to do more sophisticated magic - if you know what you are doing.)

2. Remove the NOT NULL constraint.

3. Provide a non-null value for col3 in the INSERT. You can apply or drop the same in the UPDATE. Like:

   INSERT INTO player(id, col1, col2, col3)  -- !
   VALUES (val_id, val1, val2, val3)         -- !
   ON     CONFLICT (id) DO UPDATE
   SET    col1 = excluded.col1
        , col2 = excluded.col2
   --   , col3 = excluded.col3               -- optional
   ;
   

(The same applies to col4 in your added table definition.)