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?
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.
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 ...
The ability to specify a constraint by name hasn't been implemented, but
that would read quite naturally as:
INSERT INTO mytable ON CONFLICT ON CONSTRAINT my_constraint UPDATE ...
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
