Re: text column indexing in UTF-8 database

On Thu, 2009-03-12 at 17:32 -0700, Steve Atkins wrote:

> If A=B then lower(A) = lower(B), and if A like B then lower(A) like
> lower(B).
>
> So, if nothing else, you could rewrite "where alias = 'Foo'" as
> "where lower(alias) = lower('Foo') and alias='Foo'" and take advantage
> of the lower() functional index.

Good idea. Thanks. The niggling remaining problem is that the DB is open
to a SQL-savvy audience and it'd be nice to avoid telling them to
casefold their predicates.

For regexps, lower(alias) ~* lower(regexp) won't work because extended
regexps might contain character classes (e.g., \S != \s). And, I guess
that alias ~* regexp requires a seqscan because the index isn't ordered
over ~* (right?). How about lower(alias) ~* regexp ? Is PG smart enough
to know that that ordering is well defined? Is my head on straight
there?

Thanks again,
Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0