Re: Case insensitive selects?

On Sun, 18 Feb 2001, Martijn van Oosterhout wrote:

> Tom Lane wrote:
>
> [snip]
> >
> > 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?

This is almost exactly what I was thinking of.

David