Hello, today we are going to cover how to make case insensitive query in PostgreSQL topic.

Important Note for How to Make Case Insensitive Query in PostgreSQL

Before starting i would like to highlight, charset and collation is the biggest limitation for case insensitive queries in PostgreSQL. For instance, in Turkish (Latin) alphabet i letter exists. But, beside of i, ı letter also exists. upper(i) will return İ, upper(ı) will return I. Choosing right charset for your database in the design phase is really important for those cases.

Upper() Function

UPPER() function is one of the most common method to make case insensitive search. But when you write a query like:

SELECT NAME FROM USERS WHERE UPPER(USERNAME) LIKE 'OPENBASESYSTEMS';

even you have an index on username column, PostgreSQL can not use that index, instead of this it makes a sequential search, that will also decrease search performance of the query.

Also as i mentioned in the beginning some letters like i in Turkish language will return as I and it will not be similar with your search condition.

For small tables with well designed tables you can use upper() function as quick solution.

Create Functional Index

As we discussed in last title, upper() and lower() function wont use indexes and directly makes a sequential search on the table.

You can take advantage of functional indexes for your search:

CREATE INDEX ON USERS (UPPER(USERNAME));

When you set conditions with UPPER() function, engine will use those functional indexes. Unfortunately, that means you can not use this index for lower() function.

CITEXT Module

For text fields you can use citext module to make field case insensitive. Citext stands for case insensitive text.

Here is an example for citext fields.

CREATE TABLE users (
    nick CITEXT PRIMARY KEY,
    pass TEXT   NOT NULL
);

INSERT INTO users VALUES ( 'larry',  sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Tom',    sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'NEAL',   sha256(random()::text::bytea) );
INSERT INTO users VALUES ( 'Bjørn',  sha256(random()::text::bytea) );

SELECT * FROM users WHERE nick = 'Larry';

As you can see above, first row inserted as larry but if you search as Larry, engine will return the exact row you are looking for.

ILIKE

For searches we use LIKE operator, when we need to compare values and match patterns.

ILIKE operator stands for insensitive like.

For the example above, we can revisit SQL query like:

SELECT NAME FROM USERS WHERE USERNAME ILIKE 'openbasesystems';

This will also make a case insensitive search. But again, collation is the limitation for the pattern matching.

To Sum Up

In this article we covered answer of question How to Make Case Insensitive Query in PostgreSQL?

Please leave a comment below if you have any suggestions, alternatives and questions.

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch