INSERT ... ON CONFLICT syntax issues

I'm separating this discussion out of the thread because I think this
needs wider input.

On 2015-04-24 19:21:37 -0700, Peter Geoghegan wrote:
> I've *provisionally* pushed code that goes back to the old way,
> Andres: https://github.com/petergeoghegan/postgres/commit/2a5d80b27d2c5832ad26dde4651c64dd2004f401
>
> Perhaps this is the least worst way, after all.

I still think it's a bad idea. To recap, the old and current way is:

INSERT ... ON CONFLICT (cola, colb [WHERE predicate_for_partial]) UPDATE|IGNORE

My problem with the WHERE being inside the parens in the above is that
it's
a) different from CREATE INDEX
b) unclear whether the WHERE belongs to colb or the whole index
expression. The equivalent for aggregates, which I bet is going to be
used less often, caused a fair amount of confusing.

That's why I wanted the WHERE outside the (), which requires either
adding DO between the index inference clause, and the action, to avoid
ambiguities in the grammar.

But I'm generally having some doubts about the syntax.

Right now it's
INSERT ... ON CONFLICT opt_on_conf_clause UPDATE|IGNORE.

A couple things:

a) Why is is 'CONFLICT"? We're talking about a uniquness violation. What
if we, at some later point, also want to handle other kind of
violations? Shouldn't it be ON UNIQUE CONFLICT/ERROR/VIOLATION ...
b) For me there's a WITH before the index inference clause missing, to
have it read in 'SQL' style.
c) Right now the UPDATE can refer to pseudo relations 'TARGET' and
'EXCLUDED'. I think especially the latter doesn't fit anymore at
all. How about 'CONFLICTING' and 'EXISTING'? Or even NEW and OLD?

So I guess it boils down to that I think we should switch the syntax to
be:

INSERT ... ON UNIQUE VIOLATION [WITH (cola, colb) WHERE ...] DO {NOTHING|UPDATE}

Greetings,

Andres Freund