How to Write Case-Insensitive Queries in PostgreSQL

1. Use PATTERN MATCHING in PostgreSQL
2. Use the LOWER Function to Make Cases Similar While Searching in PostgreSQL
3. the Use of CASE CONVERSION in PostgreSQL
4. Use CITEXT While Creating a Table to Perform CASE-INSENSITIVE Matching in PostgreSQL

While writing a query in our DBMS, we often notice that some characters may have to be upper or lowercase depending on the conditions required to satisfy a clause or syntax. As a result, a method for completing queries and avoiding common syntax errors must be found regardless of the circumstance.

This article will discuss how to write Case-Insensitive Queries in PostgreSQL.

Use PATTERN MATCHING in PostgreSQL

One of the ways to optimize queries in PostgreSQL where we are trying to find something in our database and have to provide it with a specific string to match along with the objects contained, we might need to use the same character cases that we used for that object while searching.

This could create problems if we forgot the exact syntax of the object’s name when it was defined earlier.

Here we can use something as simple as PATTERN MATCHING. We have a table ANIMAL with ID, AGE and TYPE.

The table already contains data as follows:

       ID     AGE   TYPE
1     2 12   "Horse"
2     1 3    "Cat"
3     3 4    "Kitten"

We can utilize the query below to find all ANIMALS with the type KITTEN.

Query:

select * from ANIMAL where type = 'Kitten';

Output:

However, if we wrote the following query:

select * from ANIMAL where type = 'kitten';

Then we’ll get nothing in our result, and this happens because the character 'k' is not equal to 'K'. To solve this, we can use the ILIKE operator to make the match case-insensitive.

Query:

select * from ANIMAL where type ILIKE 'kitten';

This query returns all animals that are of the type KITTEN. A modification to this code can be the sign operator that can be used alternatively with ILIKE.

Query:

select * from ANIMAL where type ~~* 'kitten';

Another method that we can use is the LIKE or SIMILAR TO operator, but they can only be used if we remember the parts of the name with their correct cases.

To search for KITTEN, for example, if we remember that our name had ITTEN small and Kwas either SMALL or BIG, we can write something as follows to query the result.

Query:

select * from ANIMAL where type similar to '%itten';

This is not a good alternative and should be used if the user has some idea of the naming convention used while creating objects. ILIKE provides better case-sensitive handling when matching the patterns with the string provided.

Use the LOWER Function to Make Cases Similar While Searching in PostgreSQL

A very efficient way of matching a pattern with a string can be to make the characters in both similar. Either by converting all characters to LOWER or UPPER and then matching them respectively.

PostgreSQL provides us with a function LOWER() as well as UPPER() for checking.

Query:

select * from ANIMAL where lower(type) = lower('kitten')

or

select * from ANIMAL where lower(type) = lower('kitten')

Output:

Similarly, we can use INITCAP to capitalize just the start letter of our pattern and string and the rest to lowercase to match and check.

Query:

select * from ANIMAL where initcap(type) = initcap('kitten')

the Use of CASE CONVERSION in PostgreSQL

INDEXES ON EXPRESSIONS in PostgreSQL speeds up querying results from a large table. Rather than calling the same query repeatedly on a table leading to more time, we can index it and then use it when queried.

Remember that CASE CONVERSIONS can render pre-existing INDEXES invalid as they may need to be updated again. We can write the following queries to create an index for this CASE MATCHING.

Query:

create index lower_col on ANIMAL (lower(type));

or

create index upper_col on ANIMAL (upper(type));

And similarly for INITCAP too. These INDEXES can even be used to put on constraints for ROW INSERTION where if a dataset with a different CASE is INSERTED, it can be invalidated on duplication.

To speed up LIKE and ILIKE queries, we can use GIN or GIST indexes with a PG_TRGM_EXTENSION.

Use CITEXT While Creating a Table to Perform CASE-INSENSITIVE Matching in PostgreSQL

Another alternative to the common matching in PostgreSQL is to use the CITEXT clause. It internally calls LOWER() when comparing values rather than the user putting LOWER() every time they try to match a string to a pattern.

Let’s create the table ANIMAL with the column TYPE as CITEXT.

create extension CITEXT; --creating the extension first

create table ANIMAL(
    id INT PRIMARY KEY,
    age INT,
    TYPE CITEXT
)

Then, INSERT the same values from the previous table. Now, use the following query to return a result.

select * from ANIMAL where type = 'kitten';

We can see that using CITEXT in our columns is inexpensive, more efficient, and faster than many of the solutions given above. CITEXT depends on the LC_CTYPE setting of the database, and this can be modified as per your needs.

Here are some important points about the CASE CONVERSIONS before matching.

1. Unable to Convert cases into different languages (except English).
2. LOWER() and UPPER() functions are slower due to no indexing.

These are all the different ways to compare strings with patterns In PostgreSQL.