CODEWITHSUNDEEP
sqlpostgresql
SYAHRIL ASIDIQ FIRDAUS
SYAHRIL ASIDIQ FIRDAUS

Reputation: 41

Postgresql update with values

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?

Upvotes: 0

Views: 803

Answers (2)

user330315
user330315

Reputation:

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

Upvotes: 3

TimLer
TimLer

Reputation: 1360

NOT NULL constraint

When the NOT NULL constraint is defined for a column, a row containing the null value in that column cannot be added, nor can a row be updated so as to set the null value in that column. A column for which the NOT NULL constraint is defined must have a definite value in every row. An attempt to set the null value in such a column results in a constraint violation.

In your case it means the column NAME should have a value.

Upvotes: 2

Related Questions