> > > hi David, > > My argument lives and dies on the assumption that UPSERT would be useful > even if it was (when given with no options) just a macro for > > > UPDATE db SET b = data WHERE a = key; > > IF NOT found THEN > > INSERT INTO db(a,b) VALUES (key, data); > > END IF; > > Adding things like unique indexes would work like you would expect with > individual INSERTs or UPDATEs - your statement might raise an exception. > Then, going beyond, UPSERT would optionally support atomic "a = a+1" > stuff, special actions to take on duplicate keys, all the concurrency > stuff that people have been talking about. > > IMO having such a complicated definition of what an upsert "must" be > makes it a unicorn when it could just be a sibling to INSERT and UPDATE. > > Fair enough. I'd personally much rather have a staging table and use writeable CTEs to implement something that simple - retrying on the off chance an error occurs.
I'd use UPSERT (probably still with a staging table) if I expect a high level of concurrency is going to force me to retry often and the implementation will handle that for me. To be honest though I haven't given it that much thought as I've had little need for it. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-unique-constraint-needed-for-upsert-tp5812552p5812641.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
