Case-insensitive citext type in PostgreSQL

Photo by Clément H on Unsplash

I started to work with PosgreSQL and I have one problem with string columns. They are case-sensitive. To demonstrate how PostgreSQL works let’s create a table user with a unique index. I used sequelize to work with PostgreSQL.

const User = sequelize.define('User', {
  login: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
  },
  password: {
    type: DataTypes.STRING,
    allowNull: false,
  }, {
    tableName: 'users'
  }
};

When you insert some records with psql, notice that PostgreSQL will accept “duplicated terms”.

INSERT INTO users (login, password) VALUES ('Martin', 'password');
INSERT INTO users (login, password) VALUES ('martin', 'password');

Now you can see that you have two records.

Martin
martin

We have two user with same login of lowercase/uppercase characters. It is incorrect. Fortunately PostgreSQL allows you to create indexes with expressions. You can change our login’s type to citext.

The citext module provides a case-insensitive character string type, citext. You can read about citext more in official documents. Essentially, it internally calls lower when comparing values. The standard approach to doing case-insensitive matches in PostgreSQL has been to use the lower function when comparing values, for example

SELECT * FROM tab WHERE lower(col) = LOWER(?);  

This extension calls the lower function automatically. Now, if you try to insert those same records, you’ll see that our index will prevent duplicated terms.

To enable citext, we will use psql. It is PostgreSQL interactive terminal. Then you should connect with database under a specific user.

psql -d database -U  user -W

Then you just run something like this.

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;

To list all enabled extensions on your system, run the command \dx.

psql> \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description
---------+---------+------------+--------------------------------------------------
 citext  | 1.0     | public     | data type for case-insensitive character strings
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

That’s it, you can just use case-insensitive citext type in PostgreSQL like this.

These articles was useful for me:

• Using insensitive-case columns in PostgreSQL with citext
• Create extension
• 17 Practical psql Commands That You Don’t Want To Miss

I hope the article was helpful for you. Thanks.