Postgres upsert on duplicate and how other databases implement upsert (michaelroders.tumblr.com)

33 points by ditmer 11y ago ↗ HN

7 comments

[ 1334 ms ] story [ 348 ms ] thread

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 11y 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 11y 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 11y 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 11y 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 11y ago ↗

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

craigkerstiens 11y 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...