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.
