The MERGE statement is less functional and has a LOT of hidden gotchas, I won’t argue that I can be useful but I don’t disagree when them not providing the footgun.
I won't argue that debugging MERGE statements is easy, and that there aren't pitfalls, but it's a very declarative relational model reduce pattern so calling it "less functional" doesn't seem accurate to me, if you mean functional as in functional programming. (If you mean functional as in the raw number of features supported, I also heavily disagree because I know from past experience there are reductions you can do with MERGE that you cannot do easily if at all with other "upsert" patterns.) Again, I might be biased because in that past life I was doing some wild "big data" (of a sort, of a sort) map/reduce type stuff with MERGE (in T-SQL on Microsoft SQL Server, if that matters for painting a picture).
I should have clarified for it’s intended purpose MERGE is a useful statement, but for the use case handled by INSERT .. ON CONFLICT the ANSI MERGE statement falls flat, as it does not have the same concurrency and atomicity guarantees. It’s routinely a pain point when people try to use it to implement upsert’s in MSSQL.
For other cases there is work being done by 2nd Quadrant to add MERGE into PostgreSQL, but I’m glad the other functionality was added first to avoid a common and faulty use case of MERGE.
Merge in SQLServer is horrible imo. It’s horrifically slow. And causes more problems than it solves. After learning about upsert in PostgreSQL. Ah I wish we had it in sqlserver. The PostgreSQL code is so much cleaner and more efficient.
We've all got our anecdotes, of course, but I every time I replaced a CTE, cursor, or temp table sproc with an easier to read Merge statement performance skyrocketed. It wasn't even a fair challenge because all the CTEs and cursors were written WITH (NO LOCK) because who needs consistency or transactions, but yet Merge was faster with locks and transactions than CTEs avoiding them (and kowtowing to the local "we use NO LOCK in these parts" culture left them nearly "instantaneous").
Obviously, you have to watch your execution plans and make sure everything is indexed accordingly, because the guts of almost every Merge are a JOIN and if your database isn't set up for performant joins then of course you will hate Merge's performance.
And again, certainly anecdotal and a relatively long time ago in this industry's terms that I did any of that. But still something I'm fascinated by.
[ETA: Also, yes Merge is overpowered and underperformant for single row upsert, but as soon as you need bulk upsert, chef's kiss.]
