Re: Best way to store case-insensitive data?

Where do I get info on installing this?

On Thu, Jun 10, 2010 at 2:15 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Jun 10, 2010 at 2:50 PM, Mike Christensen <mike@kitchenpc.com> wrote:
>> I have a column called "email" that users login with, thus I need to
>> be able to lookup email very quickly.  The problem is, emails are
>> case-insensitive.  I want foo@bar.com to be able to login with
>> FOO@Bar.com as well.  There's two ways of doing this, that I can see:
>>
>> 1) Every time I lookup an email in the database, do a case-insensitive
>> ilike, or cast both sides with LOWER().  I think both are slow,
>> correct?
>> 2) Every time the user updates or saves their email, store it in
>> lowercase, and every time I lookup an email, pass in a lowercase
>> email.  This is somewhat of a bug farm because one might miss some
>> little spot in a piece of code where an email is compared or updated.
>>
>> Is there any way to tell postgres to always store data in lowercase
>> form, or just have a flat out case-insensitive column?  Thanks!
>
> The contrib module citext provides a case insensitive text type.
>