Introduction
In PostgreSQL, pattern matching is an indispensable tool for querying a subset of data, particularly when looking for specific patterns within strings. Among the pattern matching commands, LIKE and ILIKE are the most commonly used to filter rows based on a specified pattern. This tutorial will guide you through their usage, enriched with practical code examples from basic to advanced.
Understanding LIKE
The LIKE operator is used in PostgreSQL to search for a specified pattern within a column. The search is case-sensitive, which means ‘A’ and ‘a’ are considered to be different characters. The patterns can include regular characters and wildcard characters: the percent sign (%) represents zero, one, or multiple characters, and the underscore (_) represents a single character.
SELECT column1 FROM table_name WHERE column1 LIKE 'pattern';Basic Usage of LIKE
Let’s start with a basic example. If you want to find all the entries that start with ‘A’ in a column named ‘name’, the query would be:
SELECT name FROM users WHERE name LIKE 'A%';If you want to find entries that end with ‘son’, you would use:
SELECT name FROM users WHERE name LIKE '%son';To find entries with ‘an’ somewhere in the middle, you would use:
SELECT name FROM users WHERE name LIKE '%an%';Case-Insensitive Search with ILIKE
On the other hand, ILIKE is the case-insensitive version of the LIKE operator. It performs the same function but does not differentiate between uppercase and lowercase letters.
SELECT column1 FROM table_name WHERE column1 ILIKE 'pattern';Example Using ILIKE
For a case-insensitive search, if you’d like to find all entries with ‘smith’, regardless of case, the query would be:
SELECT name FROM users WHERE name ILIKE '%smith%';Using LIKE with Escaping Characters
Sometimes the pattern you are searching for may actually contain wildcard characters. You can escape wildcard characters with a backslash (\) so that they are treated as regular characters. In PostgreSQL, you also have to define an escape character using ESCAPE 'character' syntax.
SELECT name FROM products WHERE description LIKE '%50\%%' ESCAPE '\';Advanced LIKE queries
PostgreSQL allows for more complex patterns. For example, combining the wildcard characters can enable you to search for more specific patterns. Let’s say you wish to find names that start with ‘A’ and are exactly five characters long:
SELECT name FROM users WHERE name LIKE 'A____';You can also look for names that start with ‘A’ and contain ‘z’ as the fourth character:
SELECT name FROM users WHERE name LIKE 'A__z%';LIKE/ILIKE with Other SQL Clauses
The LIKE and ILIKE operators can be used in conjunction with other clauses such as AND and OR for more complex queries. This approach enables you to filter records based on multiple conditions.
SELECT name FROM users WHERE name LIKE 'A%' OR name LIKE 'B%';SELECT name FROM users WHERE name ILIKE '%son' AND created_at > '2021-01-01';Performance Considerations
Using LIKE and ILIKE with a leading percent sign can lead to slower queries because it prevents the use of indexes. Whenever possible, try to use patterns without a leading wildcard, or consider full-text search capabilities for large datasets.
Patterns in Joins
We can also use LIKE and ILIKE in JOIN conditions to associate rows in a way that is pattern-sensitive. This advanced use of pattern matching can be powerful for creating complex relational data scenarios.
SELECT a.name, b.description FROM table_a a JOIN table_b b ON a.code LIKE b.code_pattern;Using LIKE/ILIKE in Subqueries
Subqueries can also employ the LIKE and ILIKE operators. This usage is typical when extracting data that matches a pattern within a subset of the data.
SELECT name FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE users.id = orders.user_id AND product_name ILIKE '%widget%');Final Words
In summary, LIKE and ILIKE are powerful operators for pattern matching in PostgreSQL, enabling you to construct queries that can search for complex patterns within your data. While LIKE is case-sensitive and ILIKE is not, both can be used in a variety of contexts, from simple searches to sophisticated joins and subqueries. Always be mindful of performance and index usage to ensure efficient querying.