Re: INSERT ... ON CONFLICT syntax issues

On 05/05/2015 12:16 AM, Peter Geoghegan wrote:
> On Sun, Apr 26, 2015 at 2:22 AM, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>> 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 ...
>
> For the record, I have made this change on Github ...

Great, thanks.

I'm a bit late to the party as I haven't paid much attention to the 
syntax before, but let me give some comments on this "arbiter index 
inference" thingie.


To recap, there are three variants:

A. INSERT ... ON CONFLICT DO NOTHING

No arbiter is specified. This means that a conflict on any unique or 
exclusion constraint is not allowed (and will do nothing instead). This 
variant is only accepted for DO NOTHING.

B. INSERT ... ON CONFLICT ON <constraint name> DO NOTHING/UPDATE

In this variant, you explicitly specify the constraint by name.

C. INSERT ... ON CONFLICT (<index params>) [WHERE <expression>] DO 
NOTHING/UPDATE

This specifies an index (or indexes, in the corner case that there are 
several identical ones), by listing the columns/expressions and the 
predicate for a partial index. The list of columns and WHERE match the 
syntax for CREATE INDEX.


That's pretty good overall. A few questions:

1. Why is the variant without specifying an index or constraint not 
allowed with DO UPDATE? I agree it might not make much sense, but then 
again, it might. If we're afraid that it's too unsafe to be the 
"default" if you don't specify any constraint, how about allowing it 
with a more verbose "ON CONFLICT ON ANY CONSTRAINT" syntax?

2. Why can't you specify multiple constraints, even though we implicitly 
allow "any" with the first variant?


Finally, a couple of suggestions. It would be pretty handy to allow:

INSERT ... ON CONFLICT ON PRIMARY KEY DO NOTHING/UPDATE

Also, I wonder if we should change the B syntax to be:

INSERT ... ON CONFLICT ON *CONSTRAINT* <constraint name> DO NOTHING/UPDATE

That would allow the syntax can be expanded in the future to specify 
conflicts on other kind of things. The "ON PRIMARY KEY" syntax should be 
unambiguous with out, because PRIMARY is a reserved keyword, but for 
example, we might want to add "ON UNIQUE INDEX <index name>" later.

- Heikki