Groups > comp.databases.postgresql > #221

Re: Case-insensitive select

Path	csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder1.news.weretis.net!news.solani.org!.POSTED!not-for-mail
From	Mladen Gogala <gogala.mladen@gmail.com>
Newsgroups	comp.databases.postgresql
Subject	Re: Case-insensitive select
Date	Wed, 31 Aug 2011 13:19:03 +0000 (UTC)
Organization	solani.org
Lines	24
Message-ID	<pan.2011.08.31.13.19.03@gmail.com> (permalink)
References	<25ee8bf2-869c-427b-a4a4-5faf86f92d57@o26g2000vbi.googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=UTF-8
Content-Transfer-Encoding	8bit
X-Trace	solani.org 1314796743 7096 eJwNyMkRwDAMAsCWAujA5VgZu/8Skn1uqlBvR2VF3rwdi60/tZlmjw+khy7axGAfOzrFBWjwAeQ7Du8= (31 Aug 2011 13:19:03 GMT)
X-Complaints-To	abuse@news.solani.org
NNTP-Posting-Date	Wed, 31 Aug 2011 13:19:03 +0000 (UTC)
User-Agent	Pan/0.133 (House of Butterflies)
X-User-ID	eJwFwQkBwDAIA0BLDSU8cgoM/xJ2x2uwdjWacrmnOF2xAuTpz0XaSVW8N1FAhDNdeD1kMvYHE0kQTg==
Cancel-Lock	sha1:X7hB5a4UlV1YC9d8GegW/Vhsd14=
X-NNTP-Posting-Host	eJwFwQkBwDAIA0BLgfLK6UjxL2F3fkJi0sLDfH0Xg5eVAXykrYBf5spBYzox5Gi9BvoJmkZxvRCf3Dqmtrm3lJqiFTw/wzkZKQ==
Xref	x330-a1.tempe.blueboxinc.net comp.databases.postgresql:221

Show key headers only | View raw

On Wed, 31 Aug 2011 05:29:13 -0700, RVince wrote:

> I'm using Postgres and the end-users are complaining that the search
> features provided by the web app are case-sensitive (via Postgres). Is
> there a way to do a case-insensitive version of select statements? Not
> all statements are of the form "=" or "like" but often of the form
> "...where lastname >= 'smith';" so the I don;t think the '~*' operator
> will work here for me in all cases. Is there a way to do this type of
> case-insensitive comparison? Is there a way to designate certain tables
> or columns be compared on a case-insensitive basis ? Thanks RVince

First of all, PostgreSQL supports ILIKE operator, which is case 
insensitive. Second, ~* will have a problem with indexes, which limits 
its usefulness. Third, PostgreSQL support standard function like "upper" 
and "lower". Your comparison can be rewritten like 
where lower(lastname) >= 'smith', which will be case insensitive. 
PostgreSQL also allows you to create an index on lower(lastname), which 
will probably be used, unless the number of Smiths in your name 
collection isn't too big for that.

-- 
http://mgogala.byethost5.com

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Case-insensitive select RVince <rvince99@gmail.com> - 2011-08-31 05:29 -0700
  Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-08-31 13:19 +0000
  Re: Case-insensitive select Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2011-08-31 20:42 +0100
    Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-08-31 23:00 +0000
      Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-01 08:56 +0200
        Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-01 12:57 +0000
          Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-01 16:40 +0200
            Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-01 21:26 +0000
              Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-02 10:08 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-02 13:03 +0000
                Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-05 09:45 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-05 10:53 +0000
                Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-05 16:13 +0200
                Re: Case-insensitive select Mladen Gogala <gogala.mladen@gmail.com> - 2011-09-06 02:21 +0000
              Re: Case-insensitive select "Laurenz Albe" <invite@spam.to.invalid> - 2011-09-02 10:08 +0200

csiph-web