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 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: 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 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