We need to use case-insensitive queries in order to get better results while performing some operations, especially search operations. Consider a case where we are searching for a specific term, but we use lowercase to specify the term to search for. The search results will return all the data where the data has occurred in the lower casing if we have used case-sensitive queries. But there might be some cases where the term is present but in upper case letters or even one upper case letter. The data associated with that term will not be returned by the research result in this case.

Under such circumstances, there comes the need for case-insensitive queries. The case-insensitive queries will return the search results without bothering about the case. Let’s see how can we query the case-insensitive queries in PostgreSQL.

How to Write Case-Insensitive Queries in PostgreSQL?

There are several ways we can write the case-insensitive queries. All the methods are enlisted and discussed below one by one:

● Using the ILIKE operator.

● Using the CITEXT Extension.

● Using the LOWER function.

● Using Case Conversion.

Let’s go over them one by one.

Method 1: Write Case-Insensitive Queries Using the ILIKE Operator

We can write case-insensitive queries by using the ILIKE operator. This operator is used for pattern-matching and particularly for case-insensitive pattern matching. Let’s see how this operator works.

Example: Case-Insensitive Search Using ILIKE

Consider the table named “simplesearch” having a column named “document” consisting of all the texts in which we will be performing the search. Let’s see what the table looks like:

Now if we want to look for the term “ate” we will write the query as:

SELECT document, document ILIKE '%ATE%' AS term_contained FROM simplesearch;

This will return all the boolean values showing the respective data/documents containing “ATE” or not irrespective of their case. The output of the query gives:

We can see that all the documents are returned along with a boolean value “true” or “false”, indicating whether "ate" is included in those documents or not. So this is how we can write case insensitive queries using the ILKIE operator.

To learn about the ILIKE operator in detail, you can head over to our article named ILIKE Operator: Case-Insensitive Pattern Matching in PostgreSQL.

Method 2: Write Case-Insensitive Queries Using the CITEXT Extension

We can use the CITEXT extension to write the case-insensitive queries. This extension allows us to declare some fields using the CITEXT data type. This data type is a case-insensitive data type that can help us perform case-insensitive text comparisons. In order to utilize this CITEXT data type, we will have to create the CITEXT extension first.

Example: Case-Insensitive Search Using CITEXT

Consider the table name “Students_Info” with CITEXT data type fields such as studentname, address, and city. The table is given as:

Now if we want to get data for “Alex” we can write the case-insensitive query as:

SELECT * FROM students_info WHERE studentname = 'aLEx';

The output of the query will return the case-insensitive comparison of the “alex”. This can be advocated by the output itself. The output is:

We can see that the query has searched for the term “Alex” regardless of the case we specified while searching.

To learn CITEXT in detail, you can refer to the article named PostgreSQL CITEXT Data Type.

Method 3: Write Case-Insensitive Queries Using the LOWER() function

We can also use the LOWER() function to write the case-insensitive queries. The LOWER() function takes in a string as a parameter and converts its case to its lowercase string. To learn about this function in detail you can see the PostgreSQL LOWER() Function article. However, we will see here how can we do case-insensitive matching/searching using the LOWER() function.

Example: Case-Insensitive Search Using LOWER()

Consider the same example as used above of the “students_info” table. The lower() function here in this case can be used like this:

SELECT * FROM students_info WHERE LOWER(studentname) = LOWER('aLEx');

The above query will search for case-insensitive matching. The LOWER(studentname) will convert all the entries of studentname as lowercase and in this table, it will search for lowercase “aLEx”.

This will return the entry where the case-insensitive matching will occur. The output is:

This is how we can use the LOWER() function to write case-insensitive queries.

Method 4: Write Case-Insensitive Queries Using the Case Conversion

Another method to write case-insensitive queries is using case conversion i.e. by creating a lower or upper function index on the column we want to perform matching. Indexing speeds up the querying results.

The query for our case can be written as:

CREATE INDEX lower_col ON students_info (LOWER(studentname));
 SELECT * FROM students_info WHERE studentname = 'alEx';

The query will do the case-insensitive text comparison by indexing. The output for this query can be written as:

The same thing can be done by using the UPPER() function as shown below:

CREATE INDEX upper_col ON students_info (UPPER(studentname));
 SELECT * FROM students_info WHERE studentname = 'alEx';

The output will again be the accurate case-insensitive matching. The output of the above query is:

So, this is how we can perform the case insensitive matching by creating a lower or upper function index on a specified column.

These were the four effective methods to write case-insensitive queries in PostgreSQL.

Conclusion

Writing the case-insensitive queries is a crucial task to do in order to perform effective searching/matching in PostgreSQL. There are four methods to write the case-insensitive matching. The first one is by using the ILIKE operator, and the second in using the CITEXT extension. Using the LOWER() function also proves to be a good approach in writing case-sensitive queries and lastly creating the lower or upper function indexes is also a helpful approach. In this article, we have learned all these methods with their implementation to make the concepts clear.