SQL insert multi rows if do not exists else update a specific column with COALESCE

Question

I have a table with two columns: id, age where id is the primary key

I know how to insert ONE new row for new primary key else update the age with the new value if the age value is not null using the following sql:

insert into obj (id, age) 
values (2, 42) 
on conflict (id) do 
update set age = coalesce(42, obj.age)

but how do I do it with multiple rows? For example:

insert into obj (id, age) 
values (2, 42), (3, 43), (5, 60) 
on conflict (id) do 
update set age = coalesce(???, obj.age)

the question is what should I put in the '???' in COALESCE?

I thought some one suggested using COALESCE(values(age), obj.age, I tried but it didn't work (syntax error).

klin · Accepted Answer · 2018-04-24 19:52:15Z

1

insert into obj (id, age) 
values (2, 42), (3, 43), (5, 60) 
on conflict (id) do 
update set age = coalesce(excluded.age, obj.age)

Note the use of the special record excluded. Per the documentation:

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.

answered Apr 24, 2018 at 19:52

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

SQL insert multi rows if do not exists else update a specific column with COALESCE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related