BUG #4920: need case-insensitive searches
The following bug has been logged online:
Bug reference: 4920
Logged by: Michael Gould
Email address: mgould@intermodalsoftwaresolutions.net
PostgreSQL version: all
Operating system: Windows 2008 Server
Description: need case-insensitive searches
Details:
I can see why people use case sensitive searches but it would be nice if
there was either a setup option in the init program or a encoding and locale
that would allow for case insenstive searches without having to use a
non-standard Ilike search. In out database for example, we have no text
fields that need a case sensitive index or search.
I'm sure I'm not the only one who could use this feature. I remember years
ago I was told that Sybase ASE was case sensitive only but the dba found a
encoding and locale that allowed for case insensitive searches.
Unless Postgres doesn't have a performance problem with upper(column) =
upper(column) then I would think it would be avantageous to support case
insensitive searches in which indexes could also be case insensitive.
Best Regards
Michael Gould
On Tue, Jul 14, 2009 at 01:28:30PM +0000, Michael Gould wrote:
I can see why people use case sensitive searches but it would be nice if
there was either a setup option in the init program or a encoding and locale
that would allow for case insenstive searches without having to use a
non-standard Ilike search. In out database for example, we have no text
fields that need a case sensitive index or search.
In 8.4, the citext module provides you a case-insensitive data type. In
earlier versions, you might store uppper(text_field) in the database instead
of or in addition to the original text_field value.
http://www.postgresql.org/docs/current/static/citext.html
--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com
On Tuesday 14 July 2009 15:37:31 Joshua Tolley wrote:
On Tue, Jul 14, 2009 at 01:28:30PM +0000, Michael Gould wrote:
I can see why people use case sensitive searches but it would be nice if
there was either a setup option in the init program or a encoding and
locale that would allow for case insenstive searches without having to
use a non-standard Ilike search. In out database for example, we have no
text fields that need a case sensitive index or search.In 8.4, the citext module provides you a case-insensitive data type. In
earlier versions, you might store uppper(text_field) in the database
instead of or in addition to the original text_field value.
Instead of an additional column you can also use an index over the expression
upper(text_field) - thats somewhat easier maintenancewise.
Andres