PostgreSQL Upsert (On Conflict) with same values in Insert and Update

Question

Can I simplify the syntax when I use the same values in an Insert... On Conflict statment?

INSERT INTO cars 
  (car_id, car_type, car_model) 
values
  (1, 'tesla', 'model s')
ON CONFLICT (car_id) DO UPDATE SET 
  car_type = 'tesla',
  car_model = 'model s';

There are many more statements of this kind because they are part of a script that gets run on every application update.

Bascially I am looking for a way to not specify the same value twice.

user330315 · Accepted Answer · 2016-10-06 09:57:35Z

11

Use the excluded keyword:

INSERT INTO cars 
  (car_id, car_type, car_model) 
values
  (1, 'tesla', 'model s')
ON CONFLICT (car_id) DO UPDATE SET 
  car_type = excluded.car_type,
  car_model = excluded.car_model;

This also works correctly with multiple rows, e.g:

INSERT INTO cars 
  (car_id, car_type, car_model) 
values
  (1, 'tesla', 'model s'), 
  (2, 'toyota', 'prius')
ON CONFLICT (car_id) DO UPDATE SET 
  car_type = excluded.car_type,
  car_model = excluded.car_model;

answered Oct 6, 2016 at 9:57

user330315

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

2 Comments

Tommy Over a year ago

This looks great. Do you have to specify the excluded columns, even if all values should be updated?

user330315 Over a year ago

@tommy: yes, unfortunately.

Collectives™ on Stack Overflow

PostgreSQL Upsert (On Conflict) with same values in Insert and Update

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related