Re: Possible to set postgres in case insensitive mode ?

On Sat, Jan 30, 2010 at 4:44 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Fri, Jan 29, 2010 at 7:40 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> wrote:
> > On Fri, Jan 29, 2010 at 2:52 PM, Moe <mohamed5432154321(at)gmail(dot)com>
> wrote:
> >> Is it possible to set postgres in case insensitive mode ?
> >>
> >> If so, how?
> >
> > What part, exactly, do you want to be case insensitive? I assume you
> > mean a text / varchar type? Look for citext, I believe it's a contrib
> > module, until 9.0 is out, which will include it natively.
>
> It's here:
> http://pgfoundry.org/projects/citext/
> But it doesn't work in 8.3 or 8.4, only 8.2 and before. So either run
> that or wait for 9.0 I guess.
>

Sorry, I forgot I posted the message. What I mean was that a query select *
from where email = ? could match on both upper and lower case emails, such
myEmail(at)hotmail(dot)com or myemail(at)hotmail(dot)com

I know I can use the lower(...) function but this is not an option when
using hibernate.

MySql is by default case insensitive, I just figured there'd be an option to
turn it on in PG as well.

What about 9.0 ? How is that going to be offered ?

For now I just normalized all emails to lower cased, and changed so that
they are always saved in lower.. but there are other columns that are likely
to need similar mathing ( firstname, lastname, address ... ) where lowering
is not an option, and where storing in a separate column is really ugly. The
like possibility is probably just slow.

Sincerely / Moe