On April 26, 2015 11:22:01 AM GMT+02:00, Heikki Linnakangas <hlinn...@iki.fi> wrote: >On 04/25/2015 12:01 PM, Andres Freund wrote: >> 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. > >Yeah, having the WHERE outside the parens seems much nicer. What is the > >ambiguity?
With a full keyword in between (like DO), there's none. But without it its ambiguous where a trailing UPDATE belongs to. At least from the point of a LALR grammar. WHERE UPDATE; is legal. I don't see the DO as much of a problem though. >> 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 >... > >As Peter said, it's also for exclusion constraints. Perhaps "ON >CONSTRAINT VIOLATION"? It doesn't apply to foreign key constraints, >though. I think "ON CONFLICT" is fine. What if we, as at least I have previously wished for, want to allow handling other types of constraints? It'd be quite cool to be able to insert the referenced key on a fkey violation for some use cases. >> b) For me there's a WITH before the index inference clause missing, >to >> have it read in 'SQL' style. > >Agreed. ON would sound more natural than WITH though: > >INSERT INTO mytable ON CONFLICT ON (keycol) UPDATE ... I chose WITh because of the repeated DO; that's all ;) --- Please excuse brevity and formatting - I am writing this on my mobile phone. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers