Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On 12/20/2014 11:14 PM, Peter Geoghegan wrote:
> On Sat, Dec 20, 2014 at 2:16 AM, Martijn van Oosterhout
> <kleptog(at)svana(dot)org> wrote:
>>> What I find curious about the opclass thing is: when do you ever have
>>> an opclass that has a different idea of equality than the default
>>> opclass for the type? In other words, when is B-Tree strategy number 3
>>> not actually '=' in practice, for *any* B-Tree opclass? Certainly, it
>>> doesn't appear to be the case that it isn't so with any shipped
>>> opclasses - the shipped non-default B-Tree opclasses only serve to
>>> provide alternative notions of sort order, and never "equals".
>>
>> Well, in theory you could build a case insensetive index on a text
>> column. You could argue that the column should have been defined as
>> citext in the first place, but it might not for various reasons.
>
> That generally works in other systems by having a case-insensitive
> collation. I don't know if that implies that non bitwise identical
> items can be equal according to the "equals" operator in those other
> systems. There aren't too many examples of that happening in general
> (I can only think of citext and numeric offhand), presumably because
> it necessitates a normalization process (such as lower-casing in the
> case of citext) within the hash opclass support function 1, a process
> best avoided.
>
> citext is an interesting precedent that supports my argument above,
> because citext demonstrates that we preferred to create a new type
> rather than a new non-default opclass (with a non-'=' "equals"
> operator) when time came to introduce a new concept of "equals" (and
> not merely a new, alternative sort order). Again, this is surely due
> to the system dependency on the default B-Tree opclass for the
> purposes of GROUP BY and DISTINCT, whose behavior sort ordering
> doesn't necessarily enter into at all.

Yeah, I don't expect it to happen very often. It's confusing to have
multiple definitions of equality.

There is one built-in example: the "record *= record" operator [1]. It's
quite special purpose, the docs even say that they "are not intended to
be generally useful for writing queries". But there they are.

I feel that it needs to be possible to specify the constraint
unambiguously in all cases. These are very rare use cases, but we should
have an escape hatch for the rare cases that need it.

What would it take to also support partial indexes?

[1] See
http://www.postgresql.org/docs/devel/static/functions-comparisons.html#ROW-WISE-COMPARISON

- Heikki