[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)