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
SQL LIKE Wildcards: %, _, and ESCAPE Explained
By Sharon Ben-Moshe · Aug 21, 2026
Use SQL LIKE for text patterns without accidental matches. Learn %, _, literal wildcard escaping, case sensitivity, and portable examples.
Read postSQL CAST vs CONVERT: Safe Type Changes Across Databases
By Sharon Ben-Moshe · Aug 21, 2026
CAST turns one SQL data type into another. Learn when CONVERT differs, how invalid values fail, and how to write portable conversions.
Read postTry it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.