PostgreSQL ILIKE: Case-Insensitive Search Explained
By Sharon Ben-Moshe · August 21, 2026 · 2 min read
Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
Use PostgreSQL ILIKE for case-insensitive pattern matching. Learn wildcards, locale behavior, indexes, alternatives, and safer search examples.
Quick answer
PostgreSQL ILIKE is the case-insensitive form of LIKE. It uses the same `%` and `_` wildcards, with matching based on the active locale. ILIKE is PostgreSQL-specific, so portable queries need a documented alternative such as a normalized comparison or a database-specific collation feature.
FAQs
What is ILIKE in PostgreSQL?
ILIKE is PostgreSQL’s case-insensitive form of LIKE. It uses the same pattern wildcards, including `%` for sequences of characters and `_` for one character.
Is ILIKE standard SQL?
No. ILIKE is PostgreSQL-specific syntax. A query that must run on multiple database products should use a documented, tested alternative for each dialect.
Does ILIKE use indexes in PostgreSQL?
Index use depends on the pattern, collation, data, PostgreSQL version, and available index type. Use EXPLAIN with the actual query rather than assuming a plain index supports every ILIKE predicate.
What is the difference between LIKE and ILIKE?
LIKE performs pattern matching with normal case behavior for the comparison. ILIKE performs case-insensitive pattern matching according to PostgreSQL’s active locale.
Keep reading
The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability
By Sharon Ben-Moshe · Oct 13, 2026
The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.
Read postThe Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes
By Sharon Ben-Moshe · Oct 13, 2026
The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.
Read postTry it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.