Pattern Matching (LIKE, regex)

PostgreSQL gives you several ways to match text against a pattern: the standard SQL LIKE operator, PostgreSQL’s case-insensitive ILIKE, full POSIX regular expressions, and the SQL-standard SIMILAR TO. Each has a different sweet spot.

LIKE and ILIKE

LIKE matches simple wildcard patterns: % matches any sequence of characters (including none), and _ matches exactly one character.

Products whose name starts with 'Wireless'

SQL

SELECT name
FROM products
WHERE name LIKE 'Wireless%';

Case-insensitive match with ILIKE

SQL

SELECT name
FROM products
WHERE name ILIKE 'wireless%';   -- matches 'Wireless', 'WIRELESS', 'wireless', ...

ILIKE is a PostgreSQL-specific convenience — plain LIKE is case-sensitive and part of the SQL standard, while case-insensitive matching in most other databases requires wrapping both sides in a function like LOWER().

POSIX regular expressions

For anything more expressive than %/_ wildcards, PostgreSQL supports full POSIX regular expressions through four operators.

Operator	Meaning
`~`	Matches the regular expression (case-sensitive)
`~*`	Matches the regular expression, case-insensitive
`!~`	Does NOT match (case-sensitive)
`!~*`	Does NOT match, case-insensitive

Emails hosted at gmail.com or yahoo.com

SQL

SELECT email
FROM customers
WHERE email ~* '@(gmail|yahoo)\.com$';

Product names that do NOT contain any digits

SQL

SELECT name
FROM products
WHERE name !~ '[0-9]';

SIMILAR TO

SIMILAR TO is the SQL standard’s answer to pattern matching — a middle ground between LIKE and full regular expressions, using %/_ alongside a small set of regex-style operators such as | for alternation.

Product codes that are 3 letters followed by 4 digits

SQL

SELECT sku
FROM products
WHERE sku SIMILAR TO '[A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9]';

Note

SIMILAR TO is standard SQL and portable across databases that implement it, but in practice PostgreSQL developers reach for ~ far more often — it is more expressive and more familiar if you already know regular expressions from another language.

Leading wildcards defeat normal indexes

A pattern like LIKE '%phone' or ~ 'phone$' cannot use a standard B-tree index efficiently, because the index is sorted left-to-right and PostgreSQL cannot know where a match might start. Patterns anchored at the start, like LIKE 'phone%', can use an index just fine. For “contains” or “ends with” searches at scale, look into trigram indexes (the pg_trgm extension) combined with a GIN index — covered later in the indexing section of this series.

LIKE / ILIKE — simple wildcards, ILIKE is PostgreSQL's case-insensitive extension
~ / ~* / !~ / !~* — full POSIX regular expressions
SIMILAR TO — SQL-standard, regex-lite, less commonly used than ~
Leading '%' or '^...$'-anchored-at-the-end patterns can't use a plain B-tree index