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.
>
>
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.