Re: sortsupport for text

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> I wasn't aware that en_US.UTF-8 doesn't have equivalence without
> equality. I guess that surprising result in my last post is just
> plain inevitable with that collation then. Bummer. Is there
> actually anyone who finds that to be a useful behavior? For a
> collation which considered upper-case and lower-case to be
> equivalent, would PostgreSQL sort as I wanted, or is it doing some
> tie-break per column within equivalent values?

Well, there are two different questions there.

As far as the overall structure of an ORDER BY or btree index is
concerned, all it knows is what the datatype comparator functions
tell it. There is no such thing as a second pass to reconsider
values found to be "equal"; that's all the info there is.

As far as the behavior of the comparator function for text is concerned,
we choose to break ties reported by strcoll via strcmp. But that's not
visible from outside the comparator, so there's no notion of an
"equivalence" behavior different from "equality".

I'm not exactly convinced that we could have a second-pass tiebreak
mechanism without creating serious problems for btree indexes; in
particular it seems like ordering considering only some leading keys
might not match the actual index ordering.

regards, tom lane