Re: Best way to store case-insensitive data?

Yup, I actually ended up doing this with this constraint:

ALTER TABLE Users ADD CONSTRAINT check_email CHECK (email ~ E'^[^A-Z]+$');

However, I like your version better so I'll use that instead :)

Mike

On Thu, Jun 10, 2010 at 11:48 PM, Adrian von Bidder
<avbidder(at)fortytwo(dot)ch> wrote:
> Heyho!
>
> On Thursday 10 June 2010 22.50:23 Mike Christensen wrote:
>> 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.
>
> I'd do it this way.  Plus either a CHECK condition on the table (email =
> lowercase(email)) (this will reliably catch all cases, but you will
> experience failures until you have found all cases)
>
> Or a BEFORE trigger that converts email to lowercase.  (This is mostly
> transparent for storing, but I usually try to avoid triggers that modify
> data like this.  But that's probably just me.)
>
> In either case, obviously you'll still need to change the code that is used
> for retrieving and comparing email addresses.
>
> cheers
> -- vbi
>
> --
> featured link: http://www.pool.ntp.org
>