3 ms·

PostgreSQL does not support the MERGE statement (at least not yet).

twic 7y ago
IIRC there was a discussion about adding the full ANSI MERGE statement, but in the end they added INSERT ... ON CONFLICT and left it at that: https://www.postgresql.org/docs/12/sql-insert.html#SQL-ON-CONFLICT https://www.postgresql.org/docs/12/sql-insert.html#SQL-ON-CO...
- WorldMaker 7y ago
  Interesting, I can see where some of my confusion comes from if it was discussed but not completed. It's somewhat disappointing as I do think the ANSI MERGE statement is a good tool.
  - snuxoll 7y ago
    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.
    - WorldMaker 7y ago
      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).
      - snuxoll 7y ago
        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.
      - philliphaydon 7y ago
        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.
        WorldMaker 7y ago
        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.]
- marcosdumay 7y ago
  I think full merge is scheduled for the next release.
johnthescott 7y ago
not really true. postgres supports "insert .. on conflict" which is functionally a subset of sql merge. the feature was introduced years ago and i would not be surprised if eventually sql merge is glommed into pg core.

twic 7y ago

IIRC there was a discussion about adding the full ANSI MERGE statement, but in the end they added INSERT ... ON CONFLICT and left it at that: https://www.postgresql.org/docs/12/sql-insert.html#SQL-ON-CONFLICT https://www.postgresql.org/docs/12/sql-insert.html#SQL-ON-CO...

WorldMaker 7y ago
Interesting, I can see where some of my confusion comes from if it was discussed but not completed. It's somewhat disappointing as I do think the ANSI MERGE statement is a good tool.
- snuxoll 7y ago
  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.
  - WorldMaker 7y ago
    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).
    - snuxoll 7y ago
      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.
    - philliphaydon 7y ago
      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.
      - WorldMaker 7y ago
        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.]
marcosdumay 7y ago
I think full merge is scheduled for the next release.

johnthescott 7y ago

not really true. postgres supports "insert .. on conflict" which is functionally a subset of sql merge. the feature was introduced years ago and i would not be surprised if eventually sql merge is glommed into pg core.