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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
