Re: UPSERT wiki page, and SQL MERGE syntax

On Wed, Oct 8, 2014 at 2:01 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Although the last go-around does suggest that there is at least one
> point of difference on the semantics. You seem to want to fire the
> BEFORE INSERT triggers before determining whether this will be an
> INSERT or an UPDATE. That seems like a bad idea to me, but if the
> consensus is that we want to do that, it does argue for your plan
> of making UPSERT a variant of the INSERT command.

Well, it isn't that I'm doing it because I think that it is a great
idea, with everything to recommend it. It's more like I don't see any
practical alternative. We need the before row insert triggers to fire
to figure out what to insert (or if we should update instead). No way
around that. At the same time, those triggers are at liberty to do
almost anything, and so in general we have no way of totally
nullifying their effects (or side effects). Surely you see the
dilemma.

> As I understand it you are proposing that would be:
>
> INSERT INTO targettable(key, quantity, inserted_at)
> VALUES(123, quantity, now())
> ON CONFLICT WITHIN targettable_pkey
> UPDATE SET quantity = quantity + CONFLICTING(quantity), updated_at = now();

That's right, yes.

--
Peter Geoghegan