[piaste]

> Postgres already has the `Insert foo on conflict do update` type syntax which I think is generally better.

INSERT... ON CONFLICT is awesome, but it has some limitations.

The one I ran into most commonly is that it can only handle exactly one unique constraint on the target table. So if you have both a PK and another unique index, you need to choose which one gets the simple 'on conflict' and which one gets a hacky workaround (locks/transactions, triggers, exception handling, etc.)

If I'm reading the MERGE docs right, you can handle that case:

   WHEN MATCHED AND old.pkey = new.pkey THEN UPDATE SET value = new.value
   WHEN MATCHED AND old.col1 = new.col1 AND old.col2 = new.col2 THEN UPDATE SET reps = reps + 1
   WHEN NOT MATCHED THEN INSERT [...]

[jtc331]

MERGE does not have locking built in in the way that ON CONFLICT does, so it handles more cases and fewer cases all at the same time.