Case-Insensitive Text Columns in Postgres with citext

#postgres #database

Recently we heard from a friend that they were curious if there was a better way to work with case-insensitive text columns in their Postgres database. They were frustrated because they found a bug where part of their application considered the column to be case insensitive and another part did not.

The column we were asked about was an email column that would be used lookup users and would certainly benefit from an index. This got us thinking about indexing case-insensitive text. Typically, the column would be defined as a text type, and everywhere it is referenced you would call lower() on the value.

That has a few drawbacks though. You have to remember to call lower() on the value, a unique constraint on the column will be case-sensitive, and it will not use an index (unless it is an index on lower(email)).

So, let's start with a typical user lookup query.

select * from user where lower(email) = lower(?);

We can improve this by changing up our table schema a bit. We are going to leverage a Postgres extension to get access to a new column type, citext (case insensitive text).

But first, we need to check to see if the extension is available on our database. This can be done by running the following query on our database or by looking at the extensions tab of our magistrate dashboard.

select * from pg_available_extensions where name = 'citext';

We got a result from running that query so we know it is available on our database. Now we can install the extension to our database with this query.

create extension if not exists citext;

Now that we have all of that set up we can change email to use the citext type we just added.

alter table users alter column email type citext;

Now our user lookup can be simplified to this query because the citext type is handling the lower() calls.

select * from user where email = ?;

This change squashed our friend's bug, and now our friend knows about citext for future case-insensitive text needs!

citext is just one of the many Postgres extensions we love. If you liked this blog post, and want to learn more about what Magistrate has to offer, sign up for our mailing list at magistratehq.com.