By default database columns are case sensitive in PostgreSQL. For the most part this is fine, but there are cases where we want a column to ignore casing. In this brief post we’ll look at how to set up case insensitive columns with PostgreSQL in the context of a Phoenix application.
What we’ll build
For the purposes of demonstration we’ll build a simple Phoenix application which contains a
Users table. The table will contain a
user_name column that we want to be case insensitive. That is we want the user names
I_am_Bob to be considered as duplicate, not distinct.
Let’s get started!
Create the app
We’ll start from scratch with a new Phoenix application and add some simple scaffolding for the User functionality.
Y when asked to fetch and install dependencies. Now we’ll change into the application directory and create the database.
Ok, our basic application is good to go, now let’s add some simple scaffolding.
Adding some scaffolding
So we won’t be adding an implementation that corresponds to a “real” user implementation, but just some simple scaffolding and fields for demonstration purposes.
As per the
mix output, let’s update the routes…
/lib/so_insensitive_web/router.ex …line 16
Before running the migration, let’s update the migration and schema slightly.
We’ll start with the migration.
We’ve updated all the fields with
null: false to indicate they are required. We’ve also added a unique index on the
user_name. This enforces the uniqueness of the
user_name field and also means look-ups based on the field will be more efficient.
Now for the schema.
We’ve kept everything the same except for the
changeset. We’ve added a number of validations to be applied against the
user_name field. We limit the username to 20 characters, add a unique constraint, and specify that it can only contain letters, numbers and the underscore character. This is a pretty common set of restrictions that one might apply to a user name.
With all that out of the way we can run the migration.
This completes our “application”, let’s have a quick look and see how our
user_name column currently acts.
If you navigate to http://localhost:4000/users you’ll see the users scaffolding we created. We can create two users with the same user name, just different casing.
If we attempt to create a third user with the same user name and casing we won’t be able to.
So our uniqueness is enforced… but it is a case sensitive uniqueness. Let’s look at how we can change this.
Making the user_name field case insensitive
The first thing we need to do is enable case insensitive columns in our PostgreSQL database. We can use a migration for this.
All this does is add the citext string type to our database. As you probably guessed, the
ci stands for case insensitive. The
citext type behaves pretty much exactly like the
text character type, but is case insensitive.
Next we’ll need to create a migration to update the
user_name field in the
Simple, we’ve updated the column type
One complication is we have existing data in our database that violates our new column type (i.e. our
I_am_Bob users). If we already had an application in production we would have a minor complication on our hands. Since this is not the case, we’ll simply drop and re-create the database with
Now let’s fire things up and see what happens.
After re-creating our
i_am_bob user, attempting to create
I_am_Bob gives us our desired behaviour.
There are some common situations where it is preferable to have case insensitive fields. Luckily this is pretty easy to set up with PostgreSQL and Phoenix. We simply need to enable the
citext module in our PostgreSQL database and then indicate that as the column type for any desired fields in our
schema file. Simple!
Thanks for reading and I hope you enjoyed the post!