Re: UPSERT wiki page, and SQL MERGE syntax

On Fri, Oct 10, 2014 at 12:04 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>
>> There is no danger of UPDATE before row-level triggers firing
>> without then updating (unless the xact aborts, but you know what
>> I mean).
>
> Well, let's make sure I do know what you mean. If a BEFORE UPDATE
> ... FOR EACH ROW trigger returns NULL, the UPDATE will be skipped
> without error, right? The BEFORE UPDATE triggers will run before
> the UPDATE if a duplicate is found, and the return value will be
> treated in the usual manner, replacing values and potentially
> skipping the update?

That's exactly right, yes (in particular, you can return NULL from
before row update triggers and have that cancel the update in the
usual manner). And potentially, the final value could be affected by
both the before row insert and before row update triggers (by way of
CONFLICTING()). That's the most surprising aspect of what I've done
(although I would argue it's the least surprising behavior possible
given my constraints).

--
Peter Geoghegan