Postgres upsert on duplicate and how other databases implement upsert(michaelroders.tumblr.com)
michaelroders.tumblr.com
Postgres upsert on duplicate and how other databases implement upsert
http://michaelroders.tumblr.com/post/119356965581/postgres-upsert-on-duplicate-and-how-other
4 comments
This needs some comment on proper usage (locking, isolation, ...) of these to avoid deadlocks (one thread inserts, other updates at the same time, ...). Without this this incomplete and of limited use.
This doesn't delve into the implementation, but does have a much deeper dive of how it all works within Postgres - http://www.depesz.com/2015/05/10/waiting-for-9-5-add-support...
i'd like to see an explanation of why this syntax was chosen instead of ANSI MERGE (http://en.wikipedia.org/wiki/Merge_%28SQL%29).
Looks like there's a thorough discussion of the pros and cons on the PostgreSQL wiki here: https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax
The semantics are slightly different - the Postgres command doesn't do the exact same thing. So rather have two identically named commands that do slightly different things, they picked a different syntax so it would be clear they aren't the same.
Some of the reasons were discussed previously: https://news.ycombinator.com/item?id=9509870
The implementation is very hard and why it wasn't in Postgresql until now.