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(at)iki(dot)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