Re: INSERT ... ON CONFLICT syntax issues

On 05/06/2015 11:05 PM, Peter Geoghegan wrote:
> On Wed, May 6, 2015 at 7:53 AM, Andres Freund <andres@anarazel.de> wrote:
>>> In this variant, you explicitly specify the constraint by name.
>>
>> I do think it's a bit sad to not be able to specify unique indexes that
>> aren't constraints. So I'd like to have a corresponding ON INDEX - which
>> would be trivial.
>
> Then what's the point of having ON CONSTRAINT? The point of it working
> that way was we're not exposing the "implementation detail" of the
> index. While I happen to think that that's a distinction without a
> difference anyway, that certainly was the idea.

Right, that's the idea. Indexes are just an implementation detail - 
conceivably you could have a constraint that's backed by some other 
mechanism. You should not embed implementation details like index names 
in your queries.

Unfortunately you can't create a "partial constraint" - you'll have to 
create a partial index. I wish we would fix that directly, by allowing 
partial unique constraints.

That said, I wouldn't necessarily be opposed to also having the syntax 
to name an index directly, as long as we had some notices in the docs to 
tell people to avoid it.

- Heikki