Case Insensitive Columns Postgres

Unsuccessful with the making columns case-insensitive framework level, I had to go deeper. Here’s how I changed my Postgres columns to be case-insensitive, and ultimately solve my model relationship problems.

CITEXT Extension for Postgres

You needs to provision the extension to the database. If you use Heroku (like me here’s a simple step).

$ heroku pg:psql --app sushi
psql (9.1.4)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

=> CREATE EXTENSION citext;
CREATE EXTENSION

The citext module provides a case-insensitive character string type, citext. Essentially, it internally calls lower when comparing values. Otherwise, it behaves almost exactly like text.

PostgreSQL 9.1.24 Documentation

Use the command \dx to check your used extensions.

DATABASE=> \dx
                                     List of installed extensions
        Name        | Version |   Schema   |                        Description                        
--------------------+---------+------------+-----------------------------------------------------------
 citext             | 1.5     | public     | data type for case-insensitive character strings
 pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

If you’re using Ubuntu install the postgresql-contrib package.

sudo apt-get install postgresql-contrib

And enable citext like this.

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;

Alter the Column: In Postgres

Alter the column type with an SQL command.

DATABASE=> ALTER TABLE "sales" ALTER COLUMN "Brand" TYPE citext;
ALTER TABLE

The ALTER TABLE return will confirm its success or failure.

Create the Column: In Your Migration

There’s a Laravel library that will let you use custom column types called Laravel Nomad. Though it seems to be built mainly for column creation, as I was unsuccessful in using it for a column ->change().

composer require shiftonelabs/laravel-nomad

class CreateUsersTable extends Migration {
    public function up()
    {
        Schema::create('users', function(Blueprint $table)
        {
            $table->increments('id');
            $table->passthru('citext', 'name');
            $table->passthru('citext', 'title')->nullable();
            $table->rememberToken();
            $table->timestamps();
        });
    }
}

Result

Now you can finally query columns and get case insensitive results.