Re: Index use for case insensitive query

On Tue, Oct 01, 2002 at 11:48:29 -0700,
  eric soroos <eric-psql@soroos.net> wrote:
> I've got a table where one of the main lookup access is by email address, which is generally a case insensitive
lookup. 
>
> With the constraints that: some of the entries aren't reall email addresses and I can't change the case of all the
databecause of that: 
>
> Is there a way to do a case insensitive index and appropriate query so that when searching for an email address, I
getthe benefit of an index? 

Yes you can make an index on a function.
You can do something like:
create index index_name on table_name (lower(column_name));

Then selects like the following should use an index:
select * from table_name where lower(column_name) = 'constant';