Re: Why is unique constraint needed for upsert? (treat atomicity as optional)

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.