Rare case sensitivity applications with Postgres

Question

I am launching a new Rails application in Postgresql and I don’t understand why on Earth they made the Postgreql case sensitive and didn’t even make a way to disable it.

I mean, really, if someone logs in as “Sam” on my site, they cannot log in as “sam,” but there can be two different accounts, “Sam” and “Sam”. This is a disaster, especially taking into account the fact that all other main databases are case insensitive.

Now instead of searching for a user like

User.find_by_name(params[:name])

I need to do this

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

I cannot believe that this cannot be avoided in Rails, because it destroys one of the basic principles of the structure: database independence.

Is there a better way to implement a case-insensitive username / email?

Answer 1

I assume that you can have two DB columns , if maintaining your case is important to you:

• One for the login name, which you will always convert to lowercase when the user signs up and which you use to login, after converting the name from the login form to lowercase
• One for the display name, which appears as the username, and exactly what the user gave you during registration

Answer 2

Contrib, citext, . , , pg 9.0.

, , Sam, Sam SAM:

abc ( ); abc_users_ci abc ( ()); abc ('sam'); abc (''); : "abc_users_ci"

! , pgsql mysql.

Answer 3

,

. Oracle, DB2 Firebird .

, .