Postgresql on conflict update?

lets say i have table like this (new_table) (all field is not-null constraints):

id    name        km_unit
1     honda       1000
2     toyota      2000
3     bmw         1000
4     wuling      1500

i want to update the table with insert with this query:

insert into new_table(id,km_unit) values
(1,20000),
(2,20000),
(3,200000),
(4,200000)
ON CONFLICT (id) 
DO 
   update SET km_unit = EXCLUDED.km_unit

but it return error like this :

null value in column "name" violates not-null constraint

the question is how to update the existing km_unit field if the id is same with values that i inserted? can i update the table without writing name field in the values?

Answer

It seems you don’t actually want to insert anything, so use an UPDATE statement:

update new_table dl
  set km_unit = v.km_unit
from (
  values 
    (1,20000),
    (2,20000),
    (3,200000),
    (4,200000)
) as v(id, km_unit)
where v.id = dl.id

Advertisement

Answer