Index use for case insensitive query

From: eric soroos <eric-psql(at)soroos(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Index use for case insensitive query
Date: 2002-10-01 18:48:29
Message-ID: 46780784.1178649587@[4.42.179.151]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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?

In the following queries, there's an index on dl_profile(_email).

test=# explain analyze select _donorNum from dl_profile where _email~'^foo(at)bar(dot)org$' ;
NOTICE: QUERY PLAN:

Index Scan using dl_profile_email on dl_profile (cost=0.00..467.75 rows=1 width=4) (actual time=14.59..14.63 rows=1 loops=1)
Total runtime: 14.97 msec

EXPLAIN
test=# explain analyze select _donorNum from dl_profile where _email~*'^foo(at)bar(dot)org$' ;
NOTICE: QUERY PLAN:

Seq Scan on dl_profile (cost=0.00..10607.28 rows=1 width=4) (actual time=4196.43..5078.86 rows=1 loops=1)
Total runtime: 5079.42 msec

thanks

eric

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2002-10-01 20:19:10 Re: How do I use the Binary AND operator in a select?
Previous Message Chris Pizzo 2002-10-01 18:41:36 How do I use the Binary AND operator in a select?