[HN Gopher] Postgres 15 Merge Command with Examples
       ___________________________________________________________________
        
       Postgres 15 Merge Command with Examples
        
       Author : winslett
       Score  : 66 points
       Date   : 2022-10-17 17:22 UTC (5 hours ago)
        
 (HTM) web link (www.crunchydata.com)
 (TXT) w3m dump (www.crunchydata.com)
        
       | throw03172019 wrote:
       | Great to see!
       | 
       | My biggest gripe with ON CONFLICT upserts are the IDs (sequences)
       | having gaps in them. Any good ways to prevent that?
        
       | baq wrote:
       | > prior to Postgres 15, you could use the "upsert" method with
       | the ON CONFLICTclause.
       | 
       | > Now, MERGE can be used instead!
       | 
       | No mention of deadlocks in the article has me worried about
       | thoroughness of the analysis.
        
         | lfittl wrote:
         | Agreed - I think MERGE is great, but this post is missing the
         | "Why not" part of the analysis.
         | 
         | In the Postgres community, MERGE has been talked about for a
         | long time, but in my understanding, part of the reason why the
         | Postgres team initially shipped INSERT ... ON CONFLICT (instead
         | of straight up MERGE) is that it lets you have guarantees about
         | the outcome of the statement (i.e. either INSERT or UPDATE, by
         | use of speculative insertion handling), vs MERGE can cause
         | unique constraint violations and other issues.
         | 
         | AFAIK, the generic syntax of MERGE does not allow for stricter
         | guarantees, and therefore there will always be cases where one
         | is better than the other.
        
       | datalopers wrote:
       | Awesome. How the hell did it take 20 years to finally land?
        
         | hn_throwaway_99 wrote:
         | > How the hell did it take 20 years to finally land?
         | 
         | Perhaps because people working for free can decide what they
         | want to work on?
         | 
         | I think merge is cool, but it also easily replicated with what
         | most of us do now for upserts in postgres using ON CONFLICT.
        
       | ryantownsend wrote:
       | This will make complex upserts so much more simple, fantastic
       | addition.
       | 
       | I really hope `RETURNING` support gets added to `MERGE` asap
       | though (I believe it's been noted as a fairly trivial addition to
       | come in future), then it'll be super powerful for doing bulk
       | upserts that require post-processing.
        
         | ei8ths wrote:
         | returning is the best.
        
       ___________________________________________________________________
       (page generated 2022-10-17 23:00 UTC)