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
isgoing 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
handleother 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
fitanymore 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