PostgreSQL - LIKE Clause

PostgreSQL - Modern Pattern Matching with LIKE, ILIKE, and Regex

PostgreSQL provides powerful operators for matching text against patterns. The most common is the LIKE operator, which uses wildcards for simple pattern matching. For more advanced needs, PostgreSQL also offers the case-insensitive ILIKE operator and full regular expression support.

The LIKE Operator

The LIKE operator returns true if a string matches a specified pattern. It is case-sensitive and uses two standard wildcards:

• The percent sign (%): Represents zero, one, or multiple characters.
• The underscore (_): Represents a single character.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

Example

Consider the COMPANY table:

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000

The following query finds all employees whose name starts with ‘P’:

SELECT * FROM COMPANY WHERE name LIKE 'P%';

Result:

id | name | age | address    | salary
----+------+-----+------------+--------
  1 | Paul |  32 | California |  20000

The ILIKE Operator (Case-Insensitive)

ILIKE is a PostgreSQL-specific extension that behaves exactly like LIKE, but the match is case-insensitive. This is extremely useful and often preferred in applications.

-- This will find 'Paul', 'paul', 'PAul', etc.
SELECT * FROM COMPANY WHERE name ILIKE 'p%';

Advanced Pattern Matching

Escaping Wildcards

If you need to search for a literal ’%’ or ’_’ character, you must use the ESCAPE clause to specify an escape character.

-- Finds products with '50%' in their name
SELECT * FROM products WHERE product_name LIKE '%50\%%' ESCAPE '\';

Regular Expressions

For patterns more complex than LIKE can handle, PostgreSQL supports POSIX regular expressions using the ~ operator.

• ~ : Case-sensitive regex match.
• ~* : Case-insensitive regex match.
• !~ : Case-sensitive regex non-match.
• !~* : Case-insensitive regex non-match.

-- Find names that end with 'en' or 'dy'
SELECT * FROM COMPANY WHERE name ~* '(en|dy)$';

This would match ‘Allen’ and ‘Teddy’.

Best Practices and Performance

Working with Non-Text Types

Pattern matching operators work on text types. If your column is numeric or another type, you must explicitly cast it to TEXT.

SELECT * FROM COMPANY WHERE salary::text LIKE '2%';

Performance Considerations

Warning: A pattern starting with a wildcard (% or _) prevents PostgreSQL from using a standard B-tree index on that column, which can lead to a full table scan and slow performance on large tables.

For applications that require fast full-text search or efficient wildcard searches, consider these solutions:

• pg_trgm Extension: Install the pg_trgm extension and create a GIN or GiST index. This allows PostgreSQL to very quickly index and search for substrings and LIKE/ILIKE patterns, even with leading wildcards.
• Full-Text Search: For searching for words and phrases in natural language documents, use PostgreSQL’s built-in Full-Text Search capabilities, which are more powerful and configurable than LIKE.

-- Example of creating a trigram index for fast LIKE/ILIKE searches
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_company_name_trgm ON COMPANY USING gin (name gin_trgm_ops);