PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Insert on conflict update not working correctly in postgres

I am using on conflict clause for insert query like this:

insert into "public"."tbl" ("id", "col1", "col2") values ($1, $2, $3) on conflict ("id") do update set "id" = $4,"col1" = $5,"col2" = $6 returning "id"

and I am getting this error:

null value in column "col3" of relation "tbl" violates not-null constraint

which doesn’t make sense, because even thos col3 has NOT NULL constraint, I am not passing it in the insert query at all, so Postgres should just take it from the existing record, which certainly does not have a null value.

Am I misunderstanding how conflict works? Do I need to pass all columns in the query? If so, there is not point in using insert .. on conflict because it does not provide any benefit compared to separate insert / update calls.

Tags:

2

Answers


  1. 0

    You must pass col3 into insert which obviously cannot be NULL, your error is not about conflict.

    Login or Signup to reply.
  2. 0

    demo at db<>fiddle

    1. If you’re on PostgreSQL 15 or above, you can switch your upserts to a merge: 
      MERGE INTO "public"."tbl" t
USING (VALUES ($1, $2, $3)) AS s("id", "col1", "col2") 
ON s."id" = t."id"
WHEN NOT MATCHED THEN--you can skip this entirely
  INSERT VALUES(s."id", s."col1", s."col2", -1)
WHEN MATCHED THEN
  UPDATE SET "id" = $4
            ,"col1" = $5
            ,"col2" = $6 
--RETURNING t."id";--only PostgreSQL v17

      If you’re on PostgreSQL 17, you can even keep the returning clause.

    2. Pass a dummy value for each column with a not null constraint: 
      insert into "public"."tbl" ("id", "col1", "col2", "col3") 
  values ($1, $2, $3, -1/*dummy*/) 
  on conflict ("id") do update 
  set "id" = $4
     ,"col1" = $5
     ,"col2" = $6 
  returning "id";
    3. If your only intention is to update because you expect zero non-conflicting rows, use an actual update. The nice thing about merge is you can
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search