Re: ON CONFLICT with constraint name doesn't work

On 03/16/2017 09:34 PM, Nikolay Samokhvalov wrote:
> I don't see anything in the current docs
> https://www.postgresql.org/docs/9.6/static/sql-insert.html saying that I
> cannot use the unique index' name here. So it definitely looks like a bug.

This is by design. The docs on conflict_target says:

"Specifies which conflicts ON CONFLICT takes the alternative action on
by choosing arbiter indexes. Either performs unique index inference, or
names a *constraint* explicitly." (emphasis mine)

As it says, you can name a constraint explicitly. A unique index is not
a constraint.

We debated this for a long time when the ON CONFLICT feature was being
developed. In the end, we settled on this behavior, on the grounds that
a constraint is a logical concept, while an index is a physical
implementation detail. Note that the SQL standard also doesn't say
anything about indexes, but constraints are in the standard.

- Heikki