Postgresql update with values

Question

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?

Your error has nothing to do with the UPDATE part. You are trying to insert rows with a NULL value for the name column, but you have a not null constraint on that column. If you only want to update the rows use an UPDATE statement. — user330315
– user330315, Commented Aug 5, 2021 at 8:48

SYAHRIL ASIDIQ FIRDAUS · Accepted Answer · 2021-08-05 10:02:26Z

3

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

edited Aug 5, 2021 at 10:02

SYAHRIL ASIDIQ FIRDAUS

415 bronze badges

answered Aug 5, 2021 at 9:13

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

@a_horse_with_no_name . . . But why would the code in the question generate a NOT NULL violation. No rows are being inserted.

user330315 Over a year ago

@GordonLinoff I assume because it first tries to insert the rows and the NOT NULL check is done before the unique constraint check.

TimLer · Accepted Answer · 2021-08-05 08:51:42Z

2

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.

answered Aug 5, 2021 at 8:51

TimLer

1,3604 silver badges16 bronze badges

2 Comments

SYAHRIL ASIDIQ FIRDAUS Over a year ago

can i use the existing values in the tabel without typing it all, like this: on conflict (id) update set name = new_table.name, km_unit = excluded.km_unit

TimLer Over a year ago

I don't think so. ON CONFLICT works to guarantee UNIQUE ENTRY and has nothing to do with NOT NULL. But you can ALTER your NAME column and set a DEFAULT VALUE for it.

Collectives™ on Stack Overflow

Postgresql update with values

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related