LIKE/ILIKE
LIKE matches text against patterns, and ILIKE does the same ignoring case.
In this page:
Syntax
WHERE column LIKE 'pattern%'
WHERE column ILIKE '%pattern%' -- case-insensitiveLIKE/ILIKE
In patterns % matches any number of characters and _ matches exactly one. LIKE is case-sensitive in PostgreSQL, ILIKE is case-insensitive, and ~ and ~* match regular expressions.
Leading wildcards such as '%abc' cannot use a normal B-tree index, though pg_trgm indexes can help. SQLite's LIKE is case-insensitive and has no ILIKE, so this is shown in psql.
Note:
For fast substring search, use a pg_trgm GIN index.
Example: LIKE/ILIKE
shop=# SELECT name FROM users WHERE name LIKE 'A%';
name
------
Ada
shop=# SELECT name FROM users WHERE name ILIKE 'a%';
name
------
Ada
alan
shop=# SELECT name FROM users WHERE name LIKE '_o_';
name
------
Bob
shop=# SELECT name FROM users WHERE name ~* '^(ada|bob)$';⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Expecting LIKE to be case-insensitive in PostgreSQL
- Using LIKE for regular expressions
- Leading wildcards on large tables
Chapter Summary
- % any characters, _ one character
- LIKE is case-sensitive
- ILIKE ignores case
- ~ matches regular expressions
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: