POSTGRESQL COLUMN CASE INSENSITIVE USING CITEXT

Author - Ankur Garach

What is PostgreSQL?

PostgreSQL is open source object-relational database system. It runs on almost all operating system like (Linux,Mac,Window etc..).

Query in PostgreSQL works same like a MySQL query. It is also called as Postgres. It also supports JSON datatype. So you can directly store JSON data.

Case Sensitive

PostgreSQL is a case sensitive.

When you insert record with same sequence of lowercase/uppercase string. It will accept a string.

 

When you retrieve a record using psql


 

It will return only one record no matter the character case you are using


 

If you retrieve both records you will have to use the LOWER function


But it is not good to write this everytime. So we can use CITEXT extension. Let’s see how can use CITEXT Extension to resolve the problem. It will be used to make case-insensitive query in PostgreSQL.

CITEXT Extension

CITEXT extension allows case insensitive column, so you can avoid lower function every time, this extension call LOWER function automatically.

You have to install the postgresql-contrib package by below command:

sudo apt-get install postgresql-contrib
 

To enable extension using  below command :

CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public;
 

To list all enabled extension using \dx command

Now you can change the column type to citext using below command:

  ALTER TABLE users ALTER COLUMN username TYPE citext;

    Now you can retrieve select query like this

   It will return both result. No matter to write LOWER function

 

Hope this blog will help people who have a problem like this. I think CITEXT extension is a nice feature to use and will be helpful to avoid writing lower function every time so you can get record properly from the database.

Don’t miss the next post!

Loading

Related Posts