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:
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:
Important Notes:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4