Re: BUG #4920: need case-insensitive searches

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