In PostgreSQL, pattern matching is performed using the LIKE, NOT LIKE, and ILIKE operators in conjunction with special wildcards. Two types of wildcards are essential for specifying patterns: the percentage sign (%) and the underscore sign (_). The percentage sign % matches any sequence of zero or more characters, while the underscore _ matches exactly one single character.
What is the LIKE Operator and How Does it Work in PostgreSQL?
The LIKE operator is used to match a search expression against a specified pattern and returns TRUE if a match is found. The critical characteristic of the LIKE operator is that it is case-sensitive. This means it treats "ABC" and "abc" as two distinct strings, resulting in a false match if the case does not align perfectly.
Use the following syntax to perform text matching using the LIKE operator:
LIKE Operator Syntax
SELECT column_list FROM tableName
WHERE col_name LIKE pattern;Use the wildcards (% or _) to define the pattern of your choice.
For a more in-depth guide on pattern construction, read our dedicated guide on Wildcards in PostgreSQL.
Example 1: How Does the LIKE Operator Work?
We start with a sample table named article_info. Its current data structure is shown below:
SELECT * FROM article_info;
We will now use the percent wildcard (%) with the LIKE operator to find the exact substring “Function” from the article_title column:
SELECT article_title FROM article_info
WHERE article_title LIKE '%Function%';
The output confirms that only two titles in the article_info table contain the substring "Function" with the specified case.
Example 2: Is the LIKE Operator Case-Sensitive?
Since the LIKE operator is case-sensitive, it will return no records if the case of the pattern does not perfectly match the data:
SELECT article_title FROM article_info
WHERE article_title LIKE '%function%';
Although the substring "Function" exists in the table, searching for "%function%" (lowercase) retrieves no records. This result definitively demonstrates the case-sensitivity of the LIKE operator.
What is the ILIKE Operator and How Does it Work in Postgres?
The ILIKE operator matches the search expression with the given pattern irrespective of the letter case. It is a PostgreSQL extension used for case-insensitive pattern matching. Use the syntax below to perform text matching using the ILIKE operator:
ILIKE Operator Syntax
SELECT column_list FROM tab_name
WHERE col_name ILIKE pattern;Example: How Does the ILIKE Operator Work?
Let’s implement the ILIKE operator on the same previous example using the lowercase pattern:
SELECT article_title FROM article_info
WHERE article_title ILIKE '%function%';
The output confirms that the ILIKE operator successfully retrieves the data irrespective of the letter case, unlike the LIKE operator.
What is the NOT LIKE Operator and How Does it Work?
The NOT LIKE operator provides the inverse functionality of the LIKE operator. It returns TRUE if the match is not found in the string and FALSE if the match is found. This operator adheres to the same case-sensitive rules as the LIKE operator.
Use the following syntax to perform negated text matching using the NOT LIKE operator:
NOT LIKE Operator Syntax
SELECT column_list FROM tableName
WHERE columnName NOT LIKE pattern;Use the wildcards (% or _) to specify your pattern.
Example: How Does the NOT LIKE Operator Work?
In this example, we use the NOT LIKE operator to find all strings that do not contain the "Function" substring in the article_title column (case-sensitive):
SELECT article_title FROM article_info
WHERE article_title NOT LIKE '%Function%'
The output shows that the NOT LIKE operator correctly retrieves all strings *other than* those that contain the specified substring “Function”.
Wrap Up
The LIKE operator performs case-sensitive pattern matching. The NOT LIKE operator negates this result (returns true if no match is found), and is also case-sensitive. Finally, the ILIKE operator performs case-insensitive pattern matching. This guide explained the usage and fundamental differences between the LIKE, NOT LIKE, and ILIKE operators for pattern matching in PostgreSQL.
V. Comparison Summary and FAQs
The table below summarizes the core behavior of the pattern matching operators in PostgreSQL:
| Operator | Function | Case Sensitivity |
|---|---|---|
| LIKE | Matches the pattern. | Yes (Case-Sensitive) |
| NOT LIKE | Excludes the pattern (Negation of LIKE). | Yes (Case-Sensitive) |
| ILIKE | Matches the pattern. | No (Case-Insensitive) |
Frequently Asked Questions (FAQ) on Pattern Matching
Q: Is there an escape character for the LIKE operator?
A: Yes, the backslash \ is the default escape character. If you need to search for a literal percentage sign or underscore, you must precede it with the escape character. Alternatively, you can explicitly define a different escape character using the ESCAPE clause, such as: WHERE col_name LIKE '100$%' ESCAPE '$'.
Q: Can I achieve case-insensitivity without using ILIKE?
A: Yes. You can convert the column data and the search pattern to a consistent case (typically lowercase) using the LOWER() function. For instance: WHERE LOWER(col_name) LIKE LOWER(pattern). However, using ILIKE is usually cleaner and potentially more efficient unless you have very specific locale requirements.
Q: When should I use the SIMILAR TO operator instead of LIKE/ILIKE?
A: The SIMILAR TO operator provides pattern matching based on SQL standard regular expressions, which is more powerful than LIKE but less powerful than PostgreSQL's native regular expression operators (~ and ~*). If your needs extend beyond simple wildcard matching (like specifying repeating groups or optional items), SIMILAR TO or the regex operators should be considered.