Re: Possible to set postgres in case insensitive mode ?

On Mon, Feb 01, 2010 at 07:35:45PM +0200, Moe wrote:
> 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.

You can use citext, a supplied module.

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

When it's ready! :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate