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@gmail.com> wrote:
> On Thu, Jun 10, 2010 at 3:34 PM, Mike Christensen <mike@kitchenpc.com> 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
>