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: Re: Why is unique constraint needed for upsert? (treat atomicity as optional)
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
