Postgres upsert on duplicate and how other databases implement upsert

http://michaelroders.tumblr.com/post/119356965581/postgres-upsert-on-duplicate-and-how-other

by ditmer • 11 years ago

33 7 11 years ago

michaelroders.tumblr.com

silon3• 11 years ago

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.

rockdoe• 11 years ago

This doesn't actually go into the "implement" part. It just describes the syntax.

The implementation is very hard and why it wasn't in Postgresql until now.

baq• 11 years ago

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

benhoyt• 11 years ago

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

swsieber• 11 years ago

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.

ashmud• 11 years ago

Some of the reasons were discussed previously: https://news.ycombinator.com/item?id=9509870

craigkerstiens• 11 years ago

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...

Postgres upsert on duplicate and how other databases implement upsert

Related Stories