Skip site navigation (1) Skip section navigation (2)

Re: Case Insensitive searches

From: "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com>
To: mgould(at)allcoast(dot)net
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Case Insensitive searches
Date: 2008-08-04 14:05:25
Message-ID: 396486430808040705m5002d334iaf08f8fee03c5107@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Mon, Aug 4, 2008 at 6:54 AM, Mike Gould <mgould(at)allcoast(dot)net> wrote:
>In some db's if you
> use a lower() or upr() it will always do a table scan instead of using a
> index

True, this would also happen in PostgreSQL.  However, you can overcome
this by creating a "functional" index:

http://www.postgresql.org/docs/8.3/interactive/indexes-expressional.html

This way all expression using where lower( column ) = 'a'. will always
use an index scan.


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

In response to

Responses

pgsql-sql by date

Next:From: Terry Lee TuckerDate: 2008-08-04 14:13:53
Subject: Re: Case Insensitive searches
Previous:From: Tom LaneDate: 2008-08-04 13:59:20
Subject: Re: return setof record - strange behavior

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group