![]() MERGE would go into its INSERT logic, and then it would get a unique violation. The comment that he essentially made is that the downside of MERGE's handling of concurrency is that when you concurrently INSERT, so at the same time as you're executing the MERGE statement, there is another INSERT going on, then MERGE might not notice that. Peter Geoghegan, who was the initial author of the INSERT ON CONFLICT feature, actually made a very helpful comment here on Twitter when people were talking about this. ![]() You might wonder what such situations are. I think that's very important to know, that there are trade offs for both of these, and you may want to use INSERT ON CONFLICT when you don't want to use MERGE. There are differences and restrictions between those two statement types and they are not interchangeable. You may also wish to consider using INSERT ON CONFLICT as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs. In the MERGE documentation you can see where it says: Now, what I did find interesting is: Why didn't Postgres have that a long time ago? Postgres 9.5 added a different syntax called INSERT ON CONFLICT. Why there is an INSERT ON CONFLICT command in Postgres I really hope that this makes it into the final release. There's different ways of writing these queries, but in general, it's very useful syntax. When it's not matched: you’re inserting a new row into the customer account table with that transaction as the starting value. Then you say when it's matched: you’re increasing the balance. ![]() The example in the above mentioned docs is: There is a customer account table, you have the recent transactions table and then you're updating the account balance, as you process that data. ![]() The other thing you can do with this is you can also use it to, for example, update a certain balance. The easiest use case, of course, is, if there is existing data, you can just make sure that you INSERT or UPDATE. I would say there's a lot of use cases I can see for this. Pretty versatile syntax here, and: these are the standard INSERT and UPDATE commands that you can utilize. And similarly, WHEN NOT MATCHED, you can also choose to INSERT the data, which will often be a use case, or DO NOTHING.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |