Re: UPSERT wiki page, and SQL MERGE syntax

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> Anything we do about triggers will by definition be novel. Right
> now, we have INSERT, UPDATE, and DELETE. If we add a new
> operation, whether it's called UPSERT or MERGE or FROB, [ ... ]

If we call it MERGE, then we had better follow the rules the
standard lays out for triggers on a MERGE statement -- which is
that UPDATE triggers fire for rows updated and that INSERT triggers
fire for rows inserted. That kinda makes sense, since the MERGE
command is almost like a row-by-row CASE statement allowing one or
the other.

If we make up our own command verb, we are free to do as we like.

> Maybe something like this:
>
> INSERT INTO targettable(key, quantity, inserted_at)
> VALUES(123, quantity, now())
> ON DUPLICATE (key)
> UPDATE SET quantity = quantity + OLD.quantity, updated_at = now();

That seems a lot cleaner than the proposal on the Wiki page. If we
go that route, it makes sense to fire the BEFORE INSERT triggers
before attempting the insert and then fire BEFORE UPDATE triggers
before attempting the UPDATE. If either succeeds, I think we
should fire the corresponding AFTER triggers. We already allow a
BEFORE triggers to run and then omit the triggering operation
without an error, so I don't see that as a problem. This makes a
lot more sense to me than attempting to add a new UPSERT trigger
type.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company