4 ms·
Postgres 15 Merge Command with Examples
- datalopers 4y agoAwesome. How the hell did it take 20 years to finally land?
- hn_throwaway_99 4y ago> 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.
- tpxl 4y agoLots of people are paid to work on Postgres.
- petesergeant 4y ago> people working for free I don’t think that is an accurate characterisation of the Postgres core team
- ttfkam 4y agoBecause it's very easy to get things wrong or cause foot guns for the user. The Postgres core team tends to err on the side of delay (or outright rejection) rather than release a feature that could lead to data loss. https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ https://www.mssqltips.com/sqlservertip/3074/use-caution-with...
- ryantownsend 4y agoThis 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 4y agoreturning is the best.
- baq 4y ago> 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 4y agoAgreed - 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.
- throw03172019 4y agoGreat to see! My biggest gripe with ON CONFLICT upserts are the IDs (sequences) having gaps in them. Any good ways to prevent that?
- loloquwowndueo 4y agoDon’t rely on sequences having no gaps? Why do you even need that?
- leetbulb 4y agofor i smell jank code
- throw03172019 4y agoI don’t rely on them having no gaps. If the sequence is an integer, it’s much quicker to wrap if the table gets a lot of upserts.
- btgeekboy 4y agoIf they're not accounting for it, integer overflow could be a real problem. (And it's not clear they would account for it in a table that normally has 10s of thousands of entries.) Someone else mentioned it here too: https://news.ycombinator.com/item?id=33242669 https://news.ycombinator.com/item?id=33242669
- eusto 4y agobigint is the default data type for sequences and it's highly unlikely that somebody would reach an overflow with it. I've worked on various database solutions, both rdbms and analytical and I find sequences to be one of the most misunderstood features in the industry. The only guarantee they make is that they generate unique values. Some of the newer distributed rdbms don't even guarantee they'll be monotonic. Relying on them generating consecutive values is a sure way to get vendor lock-in to whatever database has made that guarantee.
- 4y ago
- ramraj07 4y agoThis is one of my favorite features in snowflake (I’m assuming they didn’t invent it) and am happy to see it in Postgres now.
- alexchamberlain 4y agoThis sounds really cool; maybe slightly cleaner than an INSERT ... ON CONFLICT statement. However, is that the only advantage?
- albertopv 4y agoIn SqlServer you have to use MERGE with HOLDLOCK to avoid issues, is it possible in Postgres? Is it so by default?