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:
To enable extension using below command :
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.
