It's polite to refer to people by capitalising the first letter of their name (Joe Blogs). I think web apps should allow capitals in their usernames (JoeBlogs) too.
However it's sensible to prevent registering usernames that differ only in case (Joeblogs, JoeBlogs, joeblogs) as it would be confusing.
Further, I've had several users forget whether they capitalised their username when they log in, so the login system should ignore case.
MySQL web apps already exhibit this ideal behaviour without realising it because its search is case insensitive by default. PostgreSQL (my database of choice) is case sensitive, but has an option to perform case insensitive search in these such cases.
You could search with SQL's
ILIKE which performs case insensitive search, or
lower both the column and input when searching:
SELECT * FROM tab WHERE lower(col) = LOWER(?);. I use the Ruby on Rails engine Devise for authentication though, and digging through and monkey-patching the code doesn't seem like the best option.
The better option is to change PostgreSQL's datatype for your username column to
citext, which is a Case Insensitive text datatype. This stores case information for display, but ignores it while searching. There is a slight performance penalty, but less than with
LOWER. Plus, most of the time you should be finding users by ID, username is mainly used for login.
Before you can use it as a datatype you have to enable
citext. To add the
citext extension to your database you need to open up
psql in your terminal then run
CREATE EXTENSION citext;. Before that though you'll need to
\c into your database or the command will 'succeed' but not actually do anything to your database. Finally, it took me a while to figure out how to exit
psql; it's not
exit() but actually
CREATE EXTENSION citext;
If your production environment is on Heroku then the process is slightly different. Heroku's
pg:psql automatically connects you to your database, not the whole db server (as it might be a multi-tenant server) so you don't need to change into your database within psql.
CREATE EXTENSION citext;
If the command above failed then you are probably running an ancient version of PostgresSQL (8.3) so upgrade, or manually install citext extenstion from PGXN.
Next we change our
text column type to
citext. If you are running Rails then you'll want to generate a migration like so:
rails generate:migration ChangeUsernameToCitextOnUsers
Then open up the migration, get rid of the
change method and replace it with an
up and a
class ChangeUsernameToCitextOnUsers < ActiveRecord::Migration
change_column :users, :username, :citext
change_column :users, :username, :text
Of course you could do the same thing in plain SQL if you're not on the Rails train:
ALTER TABLE users ALTER COLUMN username TYPE citext
Yep. No need to monkey-patch your auth library in 10 different places (as I've seen suggested elsewhere), just a simple database change. I found lots of outdated information and disparate comments when searching for how to do this, hopefully this post will help out. \q