"D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> And, in fact, that's what happens if you use the operators. The only
> place they are equal is when sorting them so they can't be used as
> primary keys.
Huh? Indexes and operators are the same thing --- or more specifically,
indexes rely on operators to compare keys. I don't see how it's even
*possible* that an index would think that two keys are equal when the
underlying = operator says they are not.
A little experimentation shows that's indeed what's happening, though.
Weird. Is this a deliberate effect, and if so how did you achieve it?
It looks like what could be a serious bug to me.
> I guess there is no argument about the sorting order
> if we think they should be sorted. There is still the question of
> whether or not they should be sorted. There seems to be tacit sgreement
> but could we have a little more discussion. The question is, when inet
> or cidr is used as the primary key on a table, should they be considered
> equal. In fact, think about the question separately as we may want a
> different behaviour for each.
I'd argue that plain indexing ought not try to do anything especially
subtle --- in particular it ought not vary from the behavior of the
comparison operators for the type. If someone wants a table wherein you
can't enter two spellings of the same hostname, the right way would be
to construct a unique functional index using a function that reduces the
INET type into the simpler form. A good analogy might be a text field
where you don't want any two entries to be equal on a case-insensitive
basis. You don't up and change the behavior of indexing to be
case-insensitive, you sayCREATE UNIQUE INDEX foo_f1_key ON foo (lower(f1) text_ops);
regards, tom lane