Alex's Slip-box

These are my org-mode notes in sort of Zettelkasten style

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