Re: INSERT ... ON CONFLICT syntax issues

On 25 April 2015 at 14:05, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> > 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 ...
>
> I think that naming unique violations alone would be wrong (not to
> mention ludicrously verbose). Heikki and I both feel that the CONFLICT
> keyword captures the fact that this could be a dup violation, or an
> exclusion violation. The syntax has been like this for some time, and
> hasn't been a point of contention for a long time, so I thought this
> was settled.

I dislike the way that ignoring objections for a period leads them to be
potentially discarded. I'd prefer to think that as a community we are able
to listen to people even when they aren't continually present to reinforce
the original objection(s).

Not supporting MySQL syntax will seem like a bizarre choice to people
watching this from a distance. I accept that the patch implements useful
behaviour that MySQL does not implement and we thus provide enhanced
syntax, but the default should be match on PK using the MySQL syntax.

> Note that the syntax is quite similar to the SQLite
> syntax of the same feature, that has ON CONFLICT IGNORE (it also has
> ON CONFLICT REPLACE, but not ON CONFLICT UPDATE).

Why are we not also supporting ON CONFLICT REPLACE and IGNORE then?

If we are using syntax from other products then it should be identical
syntax, or the argument to use it doesn't stand.

We must think about what SQL Standard people are likely to say and do. If
we act as independently, our thought may be ignored. If we act in support
of other previous implementations we may draw support to adopt that as the
standard. Whatever the standard says we will eventually support, so we
should be acting with an eye to that future.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services