Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Thu, Oct 9, 2014 at 12:38 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Do not use CONFLICTING() which looks like it is a function.

So is ROW(). Or COALESCE().

> Instead, use a row qualifier, such as NEW, OLD etc to reference values
> from the incoming data
> e.g. CONFLICTING.value rather than CONFLICTING(value)
>
> Do not use the word CONFLICTING since it isn't clear whether you are
> referring to the row in the table or the value in the incoming data.

If you don't have a word that you think would more clearly indicate
the intent of the expression, I'm happy to hear suggestions from
others.

> You may also wish to support the AS keyword, as MERGE does to make the
> above even more clear.
>
> e.g. SET col = EXISTING.col + NEW.col

That's less clear, IMV. EXISTING.col is col - the very same Var. So
why qualify that it's the existing value in one place but not the
other? In fact, you can't do that now with updates in general:

postgres=# update upsert u set u.val = 'foo';
ERROR: 42703: column "u" of relation "upsert" does not exist
LINE 1: update upsert u set u.val = 'foo';
^
LOCATION: transformUpdateStmt, analyze.c:2068

This does work, which is kind of what you outline:

postgres=# update upsert u set val = u.val;
UPDATE 3

But MERGE accepts the former in other systems (in general, and for
MERGE), where Postgres won't (for UPDATEs in general). Parse analysis
of UPDATE targetlists just rejects this outright.

FWIW, is any of the two tuples reference here "NEW", in any sense?
Informally, I'd say the new value is the resulting row - the final row
value after the UPDATE. We want to refer to the existing row, and the
row proposed for insertion (with all before trigger effects carried
forward).

Having the column reference go through an alias like this might be tricky.

--
Peter Geoghegan