Making case-insensitive Queries in PostgreSQL

How to Make a "Case-Insensitive" Query in PostgreSQL?

In PostgreSQL, queries are case-sensitive by default, meaning the database will treat uppercase and lowercase letters as different. However, there are ways to make your queries case-insensitive so that they can match records regardless of text case.

Methods for Case-Insensitive Queries

1. Using ILIKE Instead of LIKE

PostgreSQL offers the ILIKE operator, which works like LIKE but is case-insensitive. This is the simplest way to perform a case-insensitive query for text comparison.

Syntax:

-- Case-insensitive query using ILIKE
SELECT * FROM table_name WHERE column_name ILIKE 'pattern';

2. Using LOWER() or UPPER() Functions with LIKE

You can also use the LOWER() or UPPER() functions to convert both the column and search term to lowercase or uppercase, making the comparison case-insensitive.

Syntax:

-- Case-insensitive query using LOWER() function
SELECT * FROM table_name WHERE LOWER(column_name) LIKE LOWER('pattern');

Examples and Code Explanation:

1. Using ILIKE Operator:

Code:

-- Select rows where 'name' column contains 'emerson' case-insensitively
SELECT * FROM employees WHERE name ILIKE '%emerson%';

Explanation:

• ILIKE '%emerson%': Matches any occurrence of "emerson" in the name column, regardless of case.

2. Using LOWER() with LIKE

Code:

-- Select rows where 'name' column contains 'emerson' case-insensitively
SELECT * FROM employees WHERE LOWER(name) LIKE LOWER('%emerson%');

Explanation:

• LOWER(name): Converts all values in the name column to lowercase.
• LIKE LOWER('%emerson%'): Matches the lowercase version of "emerson" with the lowercase values of name, achieving a case-insensitive search.

Important Notes:

• Performance Consideration: The ILIKE operator is optimized for case-insensitive searches and generally performs better than using functions like LOWER() on large datasets.
• Unicode Characters: Both methods handle Unicode characters effectively, making them suitable for internationalized applications.

All PostgreSQL Questions, Answers, and Code Snippets Collection.