Search code examples
postgresql

On Conflict Do Nothing in Postgres with a not-null constraint

Is it not possible to utilize "ON CONFLICT DO NOTHING" to avoid inserting and violating a not-null constraint?

For example...

INSERT INTO public.users (user, user_yob, sex) 
SELECT mom, mom_yob, 'F' 
FROM staging.users 
ON CONFLICT DO NOTHING;

Produces this error and stops...

INSERT INTO public.users (user, user_yob, sex) SELECT mom, mom_yob, 'F' FROM staging.users ON CONFLICT DO NOTHING
> ERROR:  null value in column "user" violates not-null constraint
  DETAIL:  Failing row contains (0b159b81-6842-4ae7-961c-2e9cff8488b1, null, null, null, null, null, null, null, null, null, F).

Ideally, this particular insert would be ignored rather than raise the error.

Solution

  • As the documentation says:

    The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error.

    NOT NULL is neither a unique nor an exclusion constraint. Technically speaking, it is not even a proper constraint in PostgreSQL.

    What you need is a BEFORE INSERT trigger that returns NULL if the NEW row contains offending null values.