Re: text column indexing in UTF-8 database

Jeff Davis <pgsql@j-davis.com> writes:
> It looks like an index using text_pattern_ops can be used for equality
> (see my test case below).

This is true as of 8.4; prior versions make a distinction between =
and ~=~.

> This works apparently because texteq() is defined as bitwise-equality.
> Is that really correct? I was under the impression that some locales do
> not obey that rule, and may consider two slightly different strings to
> be equal.

The locale might, but Postgres doesn't --- look at the implementation
of texteq().

There is actually some history here; the former distinction in the
equality operators arose from exactly your concern.  But after we
put in the second-pass check to insist on bitwise equality, we
realized that the equality operators really were equivalent.

            regards, tom lane