LIKE and ILIKE in PostgreSQL

LIKE and ILIKE in PostgreSQL: The Art of Pattern Matching


Searching for exact text matches? That’s basic level stuff. What if you need to find all records with a column that starts with "Game" or perhaps contains "Thrones"? Welcome to the world of LIKE and ILIKE in PostgreSQL, where you can perform advanced pattern matching like a pro. Let’s dive into the nitty-gritty and get you acquainted with these powerful search tools.

Basic Syntax and Usage

The LIKE and ILIKE operators are mainly used in the WHERE clause of a SELECT statement to filter records based on pattern matching. LIKE is case-sensitive, while ILIKE is case-insensitive.

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

-- OR

SELECT column1, column2, ...
FROM table_name
WHERE column_name ILIKE pattern;

Special Characters

  • %: Represents zero or more characters.
  • _: Represents a single character.

Examples with tv_series Table

Find all TV series that start with "Game":

FROM tv_series
WHERE name LIKE 'Game%';
Game of Thrones

How about series names containing the word "The," irrespective of case?

FROM tv_series
WHERE name ILIKE '%the%';
The Office
The Mandalorian
The Crown
The Witcher
The Expanse
The Sopranos
The Simpsons
The Marvelous Mrs. Maisel
The Boys
The Handmaid’s Tale
Orange is the New Black
The Good Place
The Flash

Common Errors and Solutions

Error Message:

ERROR:  syntax error at or near "LIKE"

You probably misplaced the LIKE or ILIKE keyword, or forgot to enclose the pattern in single quotes.

Review your SQL syntax carefully. Patterns should be enclosed in single quotes.

Pro Tips

  • ILIKE is PostgreSQL-specific and might not work in other SQL databases. Use it wisely if you plan to port your database.
  • Case-insensitive matching generally requires more processing, so if you don’t need it, stick with LIKE
  • To improve performance, especially in large tables, consider creating text search indexes. If your database contains a lot of text data, consider indexing to speed up LIKE and ILIKE queries, especially those with patterns that are not left-anchored.

There you go, a complete guide to LIKE and ILIKE in PostgreSQL. Want to learn more? Got a complex pattern-matching problem? Leave a comment below!

Leave a Reply