LIKE vs ILIKE in PostgreSQL: Key Differences and Usage

In PostgreSQL, LIKE is used for pattern matching with case sensitivity, while ILIKE performs case-insensitive pattern matching. Use ILIKE when you want to ignore letter case in your search patterns.

Quick Comparison

Here is a quick comparison of LIKE and ILIKE operators in PostgreSQL:

Feature	LIKE	ILIKE
Case Sensitivity	Case-sensitive	Case-insensitive
Pattern Matching	Supports % and _ wildcards	Supports % and _ wildcards
Use Case	Match exact case patterns	Match patterns ignoring case
Performance	Generally faster	Slightly slower due to case folding
Availability	Standard SQL operator	PostgreSQL-specific extension

Key Differences

The main difference between LIKE and ILIKE in PostgreSQL is how they treat letter case. LIKE matches patterns exactly, so uppercase and lowercase letters must match precisely. For example, searching for 'Apple' with LIKE will not find 'apple' or 'APPLE'.

On the other hand, ILIKE ignores case differences. It treats uppercase and lowercase letters as equal, so searching for 'Apple' with ILIKE will match 'apple', 'APPLE', or any other case variation. This makes ILIKE very useful when you want flexible, case-insensitive searches.

Because ILIKE performs case folding internally, it can be slightly slower than LIKE, especially on large datasets. However, the convenience of case-insensitive matching often outweighs this minor cost. Note that ILIKE is a PostgreSQL-specific operator and not part of the SQL standard, while LIKE is standard SQL.

Code Comparison

Example using LIKE to find names starting with 'Jo' (case-sensitive):

sql

SELECT name FROM users WHERE name LIKE 'Jo%';

Output

name
-----
John
Joanna

ILIKE Equivalent

Example using ILIKE to find names starting with 'jo' ignoring case:

sql

SELECT name FROM users WHERE name ILIKE 'jo%';

Output

name
-----
John
Joanna
JOSEPH

When to Use Which

Choose LIKE when you need exact case matching, such as searching for case-sensitive codes or identifiers. Use ILIKE when you want to find matches regardless of letter case, like user names or text fields where case should not matter. For better performance on large case-insensitive searches, consider using case-insensitive indexes or other PostgreSQL features.

Key Takeaways

LIKE is case-sensitive; ILIKE is case-insensitive.

Use ILIKE for flexible, case-insensitive pattern matching.

ILIKE is PostgreSQL-specific and slightly slower than LIKE.

Choose LIKE for exact case matches and ILIKE for ignoring case.

Consider performance and indexing when using ILIKE on large data.