Rails case sensitive applications with Postgres

Question

I'm running a new Rails app on Postgresql and I do not understand why on Earth they made Postgreql case sensitive and didn't even make an option to turn this off.

I mean, really, if someone registeres as "Sam" on my site, won't be able to log in as "sam", but there can be two different accounts "Sam" and "sam". This is a disaster, especially taking into consideration the fact that all other major databases are case insensitive.

Now instead of looking for a user like

User.find_by_name(params[:name])

I'll have to do this

User.all(:conditions=>["name ILIKE ?", params[:name]]).first

I can't believe there's no way to avoid this in Rails because it destroys one of the main principles of the framework: database independence.

Is there a better way to implement case insensitive username/e-mail schema?

Answer 1

There is a contrib module called citext which creates a case insensitive text type. I think it's only included by default starting in pg 9.0.

Also, you could easily create a unique index to prevent sam, Sam, and SAM from having an account at the same time:

create table abc (users text); create unique index abc_users_ci on abc (upper(users)); insert into abc values ('sam'); insert into abc values ('Sam'); ERROR: duplicate key value violates unique constraint "abc_users_ci"

tada! Or you could just complain that pgsql isn't like mysql.