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