Ah, I should probably upgrade to 8.4. However, I'll probably just wait for 9.0 to come out. So it seems like citext will be about the same as casting both sides to LOWER(), plus putting an index on the lowercase version of the text. I'd probably use that if it were out of the box, but I'm trying to stay away from adding too many dependencies.. I think I'll stick with my original approach of only storing lowercase data in the DB, and perhaps put a CHECK constraint on there to ensure no upper case letters sneak in. Mike On Thu, Jun 10, 2010 at 2:42 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: > On Thu, Jun 10, 2010 at 3:34 PM, Mike Christensen <mike@xxxxxxxxxxxxx> wrote: >> From this site: >> >> http://developer.postgresql.org/pgdocs/postgres/citext.html >> >> I couldn't tell if you still had to create an index on the lower case >> value. It seems that it basically mimics the WHERE LOWER(email) = >> LOWER(?) method. Since this part is incredibly performance critical, >> maybe I'm better off storing my data all in lowercase and keeping the >> DB case sensitive. > > of course you'd still need an index. whether you store it lower case > in regular text or mixed case in a citext, the db would need an index > for good performance. But you wouldn't have to store a lower() index > for citext, just an index. > > BTW, citext it new for 8.4, so it's probably not an option for you if > you're on 8.3 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
