Chapter 27: PostgreSQL LIKE Operator
The LIKE operator in PostgreSQL.
You’ve already learned SELECT, WHERE, AVG(), operators in general — now let’s master LIKE, because almost every real application needs to do “search-like” queries at some point (product search, user name lookup, email filtering, invoice number matching, etc.).
1. What does the LIKE operator actually do?
LIKE is a pattern-matching operator that compares a string value against a pattern containing wildcards.
It answers questions like:
- Does this name start with “Rah”?
- Does this product code end with “-IND”?
- Does this email contain “@gmail.com”?
- Does this description have the word “fantasy” anywhere?
PostgreSQL offers two versions of pattern matching:
| Operator | Case sensitivity | Wildcards used | Typical use case in India 2026 apps |
|---|---|---|---|
| LIKE | Case-sensitive | % and _ | Exact business rules, codes, IDs |
| ILIKE | Case-insensitive | % and _ | User-facing search (most common) |
PostgreSQL also has regular expressions (~, ~*), but LIKE / ILIKE are simpler and faster for the majority of everyday searches.
2. The two magic wildcards (understand these deeply)
| Wildcard | Meaning | Matches example | Does NOT match example |
|---|---|---|---|
| % | Zero or more of any characters | ‘Rah%’ → Rahul, Rahim, Rachel | ‘Rohit’ (doesn’t start with Rah) |
| _ | Exactly one character (any) | ‘A__a’ → Anna, Aria, Aisha | ‘Aisha’ (has 5 letters), ‘Aa’ |
Important rule: LIKE matches the entire string — not just part of it (unless you put % at both ends).
3. Basic syntax patterns (what you’ll write 95% of the time)
|
0 1 2 3 4 5 6 7 8 9 10 |
column_name LIKE 'pattern' column_name ILIKE 'pattern' -- case-insensitive version (PostgreSQL specific) column_name NOT LIKE 'pattern' column_name NOT ILIKE 'pattern' |
4. Real, practical examples (using our students and books tables)
Assume these rows exist:
students table
| id | first_name | last_name | roll_number | |
|---|---|---|---|---|
| 1 | Aarav | Patel | aarav.patel@hyduni.edu | HYD2023001 |
| 2 | Priya | Reddy | priya.reddy22@gmail.com | SEC2024005 |
| 3 | Rahul | Sharma | rahul.sharma.2004@yahoo.com | HYD2023012 |
| 4 | Sneha | Kumar | sneha.kumar06@gmail.com | HYD2023028 |
| 5 | Meera | Iyer | meera.iyer@gmail.com | HYD2024009 |
books table (partial)
| isbn | title | author_last_name |
|---|---|---|
| 978-0143414216 | The God of Small Things | Roy |
| 978-9382563754 | The Immortals of Meluha | Tripathi |
| 978-1408855683 | Harry Potter and the Philosopher’s Stone | Rowling |
Example 1: Names starting with certain letters
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Students whose first name starts with 'A' or 'a' (case-sensitive) SELECT first_name, last_name FROM students WHERE first_name LIKE 'A%'; -- Better: case-insensitive (most real apps use this) SELECT first_name, last_name FROM students WHERE first_name ILIKE 'a%'; |
→ Returns: Aarav, (maybe others starting with A/a)
Example 2: Ends with something
|
0 1 2 3 4 5 6 7 8 9 |
-- Emails that end with gmail.com SELECT email FROM students WHERE email ILIKE '%@gmail.com'; |
→ Returns: priya.reddy22@gmail.com, sneha.kumar06@gmail.com, meera.iyer@gmail.com
Example 3: Contains a word anywhere
|
0 1 2 3 4 5 6 7 8 9 |
-- Book titles containing "Potter" (case-insensitive) SELECT title FROM books WHERE title ILIKE '%potter%'; |
→ Returns: Harry Potter and the Philosopher’s Stone
Example 4: Exact length or pattern with _
|
0 1 2 3 4 5 6 7 8 9 |
-- Roll numbers that are exactly 10 characters and start with HYD SELECT roll_number FROM students WHERE roll_number LIKE 'HYD______'; -- 3 + 7 = 10 chars |
→ Matches: HYD2023001, HYD2023012, HYD2023028
Example 5: NOT LIKE (exclude patterns)
|
0 1 2 3 4 5 6 7 8 9 |
-- Students NOT from Hyderabad codes SELECT first_name, roll_number FROM students WHERE roll_number NOT LIKE 'HYD%'; |
→ Returns: Priya Reddy (SEC…)
Example 6: Combining LIKE with other conditions
|
0 1 2 3 4 5 6 7 8 9 10 |
SELECT first_name, last_name, email FROM students WHERE (first_name ILIKE 'r%' OR last_name ILIKE 'r%') AND email NOT ILIKE '%yahoo.com' ORDER BY first_name; |
5. Common traps & best practices (real student & production lessons)
| Mistake / Question | Wrong / Surprising result | Correct / Explanation |
|---|---|---|
| LIKE ‘rahul’ | Matches only exactly “rahul” | Use %rahul% if you want contains |
| LIKE ‘%rahul’ | Matches only ends with rahul | Use %rahul% for contains |
| LIKE ‘Rahul’ on column with ‘rahul’ | No match (case-sensitive) | Use ILIKE for user-facing search |
| LIKE ‘%’ | Matches everything (including NULL) | Careful — usually you want IS NOT NULL too |
| Performance on large table | Slow if no index | Add trigram index (pg_trgm) for %term% searches |
| Escaping % or _ in pattern | % or _ treated as wildcard | Use ESCAPE or escape manually: LIKE ‘%10\% off’ |
Best practice in 2026 Indian apps:
|
0 1 2 3 4 5 6 7 |
-- Almost always use ILIKE for user search WHERE name ILIKE '%' || $1 || '%' |
(Prepared statement / parameter)
When LIKE is too slow for contains-search → switch to pg_trgm extension + <% operator or full-text search.
6. Your mini practice right now (try in your database)
- Find all students whose email ends with @gmail.com (use ILIKE)
- Find books whose title contains “the” (case-insensitive)
- Find roll numbers that have “2023” anywhere in them
- Find students whose first name is exactly 5 letters long (use _)
Next topic?
Tell me:
- Want deep dive into regular expressions (~ / ~*) vs LIKE?
- pg_trgm extension for fast fuzzy / contains search?
- Full-text search (@@ operator) for natural language search?
- Or move to another aggregate / string function (CONCAT, SUBSTRING, etc.)?
Your teacher is ready — just say the word! 🚀
