Re: Case insensitive selects? - Mailing list pgsql-general

From David Wheeler
Subject Re: Case insensitive selects?
Date
Msg-id Pine.LNX.4.21.0102171049460.30549-100000@theory
Whole thread Raw
In response to Re: Case insensitive selects?  (Martijn van Oosterhout <kleptog@svana.org>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Function reference
Next
From: Feite Brekeveld
Date:
Subject: dump of dictionary