Two years ago I wrote about MERGE arriving in PostgreSQL 15 and listed three restrictions I had found by trying: no RETURNING, no views as a target, and no way to say "rows in the target the source never mentioned".

PostgreSQL 17 was released on the twenty-sixth of September and it closes all three. It is rare to get to check a list that cleanly, so here is the follow-up — including the complaint that did not get fixed, which is the one that matters.

The three that closed

RETURNING now works, which means a MERGE can tell you what it did instead of requiring a second statement to find out:

MERGE INTO device_state AS t
USING incoming_batch   AS s
   ON t.device_id = s.device_id
WHEN MATCHED THEN
  UPDATE SET last_seen = s.last_seen
WHEN NOT MATCHED THEN
  INSERT (device_id, last_seen) VALUES (s.device_id, s.last_seen)
RETURNING merge_action(), t.device_id;

merge_action() is the part that makes it genuinely useful — it reports which branch produced each returned row, so a single statement can drive whatever comes next without you inferring the action from the data.

Views can be targets, provided the view is automatically updatable with no INSTEAD OF triggers, or has INSTEAD OF triggers for every action the statement uses. Views with rules are still out.

And the third one, which was the gap I felt most:

WHEN NOT MATCHED BY SOURCE AND t.status <> 'archived' THEN
  UPDATE SET status = 'archived'

That is "a row exists in the target and the batch did not mention it". With it, MERGE can finally express the full reconcile — insert what is new, update what changed, retire what has gone — in one statement over one join. That was the job people reached for MERGE to do in 15, and in 15 it did not quite reach.

The symmetric spelling WHEN NOT MATCHED BY TARGET is now available for the insert case too, and it is an extension rather than standard SQL, which is worth knowing if the query has to run anywhere else.

The one that did not close, and should not have

The concurrency behaviour is unchanged, and I want to be clear that this is correct rather than an oversight.

MERGE still evaluates its join and then acts on the result. Two concurrent statements can both find no match for the same key, both take the not-matched branch, and one of them will hit the unique index. INSERT ... ON CONFLICT still cannot do that to you, because it discovers the conflict through the index at insert time rather than deciding in advance.

That is not a missing feature. It follows from what the statement is. A join-driven statement with several branches has to decide which branch applies before it acts, and any decision made in advance can be invalidated by a concurrent writer. You could only remove it by making MERGE into something else.

So the rule from two years ago survives the release intact. If the operation fits ON CONFLICT, use ON CONFLICT — it is narrower, non-standard, and correct under concurrency without any effort. Reach for MERGE when the thing you are expressing really is a join with branches, when it runs as a batch, and when you can answer who else is writing those rows.

What changed is that the second category got considerably larger, because the statement can now express the whole reconcile instead of most of it.

The rest of the release

Worth a mention because two of them are more likely to affect a service than MERGE is.

JSON_TABLE converts JSON into a relational result set, which removes a category of lateral-join-and-unnest query that nobody enjoys writing. The SQL/JSON constructors and query functions from the standard landed alongside it.

And there is substantial work on vacuum memory management and on the write-ahead log under concurrent writes. That is the unglamorous kind of release note that shows up as a quieter database rather than a feature anybody demos.

What I would tell 2022

That the restrictions were a release-timing artefact rather than a design position, and worth waiting out. That the concurrency difference was the real finding and the one to write down, because it was a property of the statement rather than a gap in it.

And that the useful habit is the one that produced this post: when a feature lands incomplete, write down precisely what it could not do. Two years later that list is either a changelog you can check in ten minutes, or it is evidence that the thing you wanted was never coming — and both of those are worth more than a vague memory of having been disappointed once.