MERGE arrives in PostgreSQL 15

A supplier price list, loaded into staging_prices, then reconciled against prices. Since PostgreSQL 15 came out two weeks ago, that is one statement:

MERGE INTO prices p
USING staging_prices s ON p.sku = s.sku
WHEN MATCHED AND s.price IS NULL THEN
    DELETE
WHEN MATCHED AND p.price <> s.price THEN
    UPDATE SET price = s.price, updated_at = now()
WHEN NOT MATCHED THEN
    INSERT (sku, price) VALUES (s.sku, s.price);

Before 15 this was three statements in a transaction, or a stored procedure, or a loop in PHP. Now it is one statement that says what it does. Update changed rows, delete withdrawn ones, insert new ones. People coming from Oracle and SQL Server waited a decade for this. The conditional WHEN MATCHED AND ... branches are the real value. ON CONFLICT cannot express “delete when the source says so” at all.

But MERGE does not replace INSERT ... ON CONFLICT, and this is the part worth remembering. ON CONFLICT uses speculative insertion. Two concurrent transactions upserting the same key both succeed, one waits for the other, no error. MERGE gives no such promise. It checks for a match, then acts, and between the check and the insert another transaction can slip in. Under concurrency MERGE can fail with a unique violation, and your code must be ready to retry. The docs say this directly. Few will read that paragraph, and some of them will meet it in production at 2am.

So my split. Concurrent upsert of single rows from application code, the counter, the session, the cache row: ON CONFLICT, as before. Batch reconciliation, migrations, one writer moving a dataset into place: MERGE.

And if the logic fits in ON CONFLICT DO UPDATE, keep it there. The simpler statement is also documentation. MERGE with five branches is powerful, and the person reading it after you will need coffee.

I wrote a five-branch one the first evening. It was beautiful. I rewrote it the next morning.