How to Use ILIKE in PostgreSQL for Case-Insensitive Matching

In PostgreSQL, use the ILIKE operator to perform case-insensitive pattern matching in WHERE clauses. It works like LIKE but ignores letter case, making it easy to find matches regardless of uppercase or lowercase letters.

Syntax

The ILIKE operator is used in a WHERE clause to filter rows based on a case-insensitive pattern match.

• column_name ILIKE pattern: Checks if the column matches the pattern ignoring case.
• pattern can include wildcards: % for any sequence of characters, _ for a single character.

sql

SELECT * FROM table_name WHERE column_name ILIKE 'pattern';

Example

This example shows how to find all users whose names contain 'ann' in any letter case.

sql

CREATE TEMP TABLE users (id SERIAL PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES
('Anna'), ('anne'), ('ANNABELLE'), ('Bob'), ('Annie');

SELECT id, name FROM users WHERE name ILIKE '%ann%';

Output

 id |   name   
----+----------
  1 | Anna
  2 | anne
  3 | ANNABELLE
  5 | Annie

Common Pitfalls

One common mistake is using LIKE when you want case-insensitive matching; LIKE is case-sensitive in PostgreSQL.

Another pitfall is forgetting to use wildcards % or _ in the pattern, which can lead to no matches.

sql

/* Wrong: case-sensitive search, misses 'anne' and 'ANNABELLE' */
SELECT name FROM users WHERE name LIKE '%ann%';

/* Right: case-insensitive search with ILIKE */
SELECT name FROM users WHERE name ILIKE '%ann%';

Quick Reference

Operator	Description	Case Sensitivity
LIKE	Pattern match with wildcards	Case-sensitive
ILIKE	Pattern match ignoring case	Case-insensitive
%	Matches any sequence of characters	N/A
_	Matches any single character	N/A

Key Takeaways

Use ILIKE in PostgreSQL for case-insensitive pattern matching.

Remember to include wildcards like % to match parts of strings.

LIKE is case-sensitive; use ILIKE to ignore case.

Patterns can use % for any characters and _ for a single character.

Test queries to ensure they return expected results with different letter cases.