Case Insensitive Usernames with PostgreSQL citext

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.

The Options for Case Insensitive Search in Postgres

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.

A PSQL Poem to Enable citext

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 ctrl+c or q or exit() but actually \q.

psql

\c database_name
CREATE EXTENSION citext;
\q

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.

heroku pg:psql

CREATE EXTENSION citext;
\q

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.

Marvellous Migration, or Simple SQL

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 down:

class ChangeUsernameToCitextOnUsers < ActiveRecord::Migration
  def up
    change_column :users, :username, :citext
  end

  def down
    change_column :users, :username, :text
  end
end

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

And, er, that's it!

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