Re: Case insensitive selects?

Tom Lane wrote:

[snip]

> > Hmmm...I'd hate to have two indexes on every field I query like this, one
> > case-senstive, one case-insensitve (like the one you create here). Is
> > there a configuration option or something that will tell pgsql to do
> > case-insensitive comparisons (kinda like MS SQL Server has)? That could
> > save us on indexing overhead, since we want all of our WHERE comparisons
> > to be case-insensitive, anyway.
>
> Then why are you bothering to maintain a case-sensitive index?
>
> There's no free lunch available here; if you think there is, then you
> are misunderstanding what an index is. Either the index is in
> case-sensitive order, or it's not.

I've actually been thinking about this and maybe this is possible with
some smarts in the query parser. If you have an index on
lower(fieldname) then consider the following query:

select *
from table1, table2
where table1.a = table2.b;

(the index is on lower(table1.a).

Now, it should be true that a = b implies lower(a) = lower(b), so the
above query is equivalent to:

select *
from table1, table2
where table1.a = table2.b
and lower(table1.a) = lower(table2.b);

This query can use the index and produce the correct result. Am I
missing anything?
--
Martijn van Oosterhout <kleptog(at)cupid(dot)suninternet(dot)com>
http://cupid.suninternet.com/~kleptog/