CODEWITHSUNDEEP
postgresqlpostgresql-9.6
Tommy
Tommy

Reputation: 656

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

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.

Upvotes: 4

Views: 2349

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 11

Related Questions