From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | eric soroos <eric-psql(at)soroos(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Index use for case insensitive query |
Date: | 2002-10-01 21:05:50 |
Message-ID: | 20021001210550.GA27859@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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';
From | Date | Subject | |
---|---|---|---|
Next Message | Cameron Moller | 2002-10-02 02:53:05 | word / line wrap at the terminal |
Previous Message | Tom Lane | 2002-10-01 20:19:10 | Re: How do I use the Binary AND operator in a select? |