Re: ON CONFLICT with constraint name doesn't work

On Thu, Mar 16, 2017 at 1:59 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> I don't think that's an appropriate fix. ISTM we should say 'violates
> unique index' when it's just an index and 'violates unique constraint'
> when the index is backing a constraint.
>

To me, it now seems to be correct as well.

From what I see experimenting with unique indexes/constraints and looking
to "pg_constraint" and "pg_indexes" catalogs:
a) if there is a unique constraint created by user, there is always the
corresponding unique index defined, with the same name; and renaming of the
index leads to implicit renaming of the constraint;
b) in contrast, creation of a unique index does not automatically lead to
creation of the corresponding unique constraint;
c) any primary key is also a unique index by definition (in Postgres
context, it's not a "unique constraint", it's a "unique index").

So violation of uniqueness is always a violation of a unique index, in all
three cases. However, case (b) is very tricky and I suspect that many users
will be consused -- just like I was today. Anyway, the proposed patches
makes messaging and docs closer to the current implementation, minimizing
the possible confusion.

Also, I assume that in the future, there is a possibility to distinguish
cases "violates unique constraint", "violates primary key" and "violates
unique index" – as I know, in Oracle, for example, you can have a
*deferrable* unique constraint based on non-unique, regular index...

Anyway, attached are 2 separate patches:
1) version 2 of patch fixing the message, including regression tests;
2) proposed change to the documentation
https://www.postgresql.org/docs/current/static/sql-insert.html