Thread: 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 loc=
ale
> 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.=20=20

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.
>
> http://www.postgresql.org/docs/current/static/citext.html
Instead of an additional column you can also use an index over the expression
upper(text_field) - thats somewhat easier maintenancewise.

Andres