Conversation

MERGE is most noticeable, but will we prefer it over INSERT … ON CONFLICT, given the fact that it lacks RETURNING and, most importantly, it will be failing with errors when multiple sessions target the same rows? Does it have good use cases except migration from ora/ms?

Quote Tweet

Craig Kerstiens

@craigkerstiens

Oct 14

Pretty sure this is the feature everyone is most excited about with Postgres 15 - MERGE crunchydata.com/blog/a-look-at

Replying to

MERGE is a truly declarative statement for reconciling one table with another. It can use any available join strategy, which can make performance robust even as things like the underlying cardinality change (like any optimizable statement).

Replying to

and

It seems like it should usually be easy to decide if you should use MERGE, or prefer ON CONFLICT. It comes down to this: do you want to do an upsert, or something more general? ON CONFLICT offers extra guarantees (even in READ COMMITTED), at the cost of some generality.

1:45 AM · Oct 15, 2022Twitter Web App

Replying to

and

Arguably MERGE has race conditions, but context/expectations are important. Has race conditions relative to what? With what transaction isolation level, under what circumstances? You can have the same race conditions with multiple statements, too.

Replying to

And RETURNING :) Very often, I want to see the result. So far, it looks like ON CONFLICT will be the default choice for me -- but maybe I'll find some precise use case for MERGE at some point (beyond migrations).

Replying to

Yeah, I can't argue with you about RETURNING 🙂