Re: Index use for case insensitive query

On Tue, Oct 01, 2002 at 11:48:29 -0700,
eric soroos <eric-psql(at)soroos(dot)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 data because of that:
>
> Is there a way to do a case insensitive index and appropriate query so that when searching for an email address, I get the 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';