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

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.

--
Peter Geoghegan