20180925150335-on_conflict_(upsert).org

ON CONFLICT (UPSERT)

ON CONFLICT (UPSERT)

You can use ON CONFLICT (column_name) to perform a different action when trying to insert a record. For example, you can update the existing record instead of just having a constraint exception raised.

NOTE: the columns used in ON CONFLICT must be backed by a database constraint.

UPSERT

INSERT INTO vehicle_make_model_tiers (make, model, tier) 
VALUES ('A', 'B', 1), ('A', 'C', 2)
ON CONFLICT (make, model)
DO UPDATE SET tier = EXCLUDED.tier

• In this example we have a unique, compound index for make and model
• So, when a conflict arises, instead of just throwing the exception…
• Use DO followed by the action.
• You could DO NOTHING or DO UPDATE ...
• Use EXCLUDED.column_name to access the values of the conflicting insert attempt

Resources

• PostgreSQL Upsert Using INSERT ON CONFLICT statement
• Upsert Records with PostgreSQL 9.5 | Hashrocket
• upsert_all in Ruby on Rails