On Tue, 26 Jul 2005, Joseph Shraibman wrote:
>
>
> Stephan Szabo wrote:
> > On Tue, 26 Jul 2005, Joseph Shraibman wrote:
> >
> >
> >>I have this index:
> >>
> >>"directory_lower_username_seg_key" unique, btree (lower(username)
> >>text_pattern_ops, seg)
> >>
> >>... but my query refuses to use that index.
> >
> >
> > text_pattern_ops is an opclass for doing LIKE queries using the index, I
> > don't believe it's used for equality comparisons.
>
> Seems like a bug to me. That operator can't do the operations equal,
> less than, and greater than?
It is for the operators ~<~, ~<=~, ~=~, ~>=~, ~>~ (for like optimization).
The docs seem to say that it does a character by character comparison
rather than one using the collation thus being better for pattern
matching. I'd think letting it do <, <=, =, >=, > would have it giving the
wrong results for such queries (well, in non-C locales).