Re: UPSERT wiki page, and SQL MERGE syntax

On Thu, Oct 16, 2014 at 6:48 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> If that seems too complicated, leave it out for v1: just insist that
> there must be at least one unique non-partial index on the relevant
> set of columns.

That's what I'll do.

> There seems to be some confusion here. This part was about this syntax:
>
>>>>> INSERT INTO overwrite_with_abandon (key, value)
>>>>> VALUES (42, 'meaning of life')
>>>>> ON DUPLICATE (key) UPDATE;
>
> That's a different issue from naming indexes.

It is? In any case, I'm working on a revision with this syntax:

postgres=# insert into upsert values (1, 'Foo') on conflict (key)
update set val = conflicting(val);
INSERT 0 1
postgres=# insert into upsert values (1, 'Foo') on conflict (val)
update set val = conflicting(val);
ERROR: 42P10: could not infer which unique index to use from
expressions/columns provided for ON CONFLICT
LINE 1: insert into upsert values (1, 'Foo') on conflict (val) updat...
^
HINT: Partial unique indexes are not supported
LOCATION: transformConflictClause, parse_clause.c:2365

Expression indexes work fine with this syntax.

I want to retain CONFLICTING(), although I'm thinking about changing
the spelling to EXCLUDED(). While CONFLICTING() is more or less a new
and unprecedented style of expression, and in general that's something
to be skeptical of, I think it's appropriate because what we want here
isn't quite like any existing expression. Using an alias-like syntax
is misleading, since it implies that are no effects carried from the
firing of before row insert triggers. It's also trickier to implement
alias-like referencing.

This is not a join, and I think suggesting that it is by using an
alias-like syntax to refer to excluded rows proposed for insertion
from the UPDATE is a mistake.

--
Peter Geoghegan