LIKE

The LIKE operator in PostgreSQL is used for pattern matching on string values. It is commonly used in WHERE clauses to search for rows where a column value matches a partial string pattern.

LIKE wildcards

PostgreSQL LIKE supports two wildcard characters:

• % (percent) — matches any sequence of zero or more characters
• _ (underscore) — matches exactly one character

Basic syntax:

value LIKE pattern

Returns true if value matches pattern. To negate:

value NOT LIKE pattern

If the pattern contains no wildcard characters, LIKE behaves identically to the = operator.

PostgreSQL LIKE examples

Match names starting with "Jen":

SELECT first_name, last_name
FROM customer
WHERE first_name LIKE 'Jen%';

first_name | last_name
------------+-----------
 Jennifer   | Davis
 Jennie     | Terry
 Jenny      | Castro
(3 rows)

Match names containing "er" anywhere:

SELECT first_name, last_name
FROM customer
WHERE first_name LIKE '%er%'
ORDER BY first_name;

first_name  | last_name
-------------+------------
 Albert      | Crouse
 Alberto     | Henning
 Alexander   | Fennell
 Amber       | Dixon
...

Using both wildcards — match names with any first character followed by "her":

SELECT first_name, last_name
FROM customer
WHERE first_name LIKE '_her%'
ORDER BY first_name;

first_name | last_name
------------+-----------
 Cheryl     | Murphy
 Sherri     | Rhodes
 Sherry     | Marshall
 Theresa    | Watson
(4 rows)

The pattern _her% requires: one character (any), then literally "her", then anything.

ILIKE — case-insensitive matching

PostgreSQL extends standard SQL with the ILIKE operator for case-insensitive pattern matching:

SELECT first_name, last_name
FROM customer
WHERE first_name ILIKE 'BAR%';

first_name | last_name
------------+-----------
 Barbara    | Jones
 Barry      | Lovelace
(2 rows)

The same query with LIKE 'BAR%' would return zero rows because it performs case-sensitive matching.

Matching literal wildcard characters with ESCAPE

If the data you are searching contains % or _ as literal characters, use the ESCAPE option to treat them as regular characters:

SELECT message FROM t
WHERE message LIKE '%10$%%' ESCAPE '$';

In this pattern, $% means a literal percent sign — the $ is the escape character.

LIKE operator shorthand symbols

PostgreSQL also supports symbolic equivalents of LIKE operators:

• ~~ is equivalent to LIKE
• ~~* is equivalent to ILIKE
• !~~ is equivalent to NOT LIKE
• !~~* is equivalent to NOT ILIKE

Performance tips for LIKE

• Leading wildcard kills index usage: patterns like LIKE '%term%' or LIKE '%term' cannot use a standard B-tree index and require a full sequential scan. For full-text search needs, use pg_trgm (trigram) indexes or PostgreSQL full-text search.
• Trailing wildcard is index-friendly: LIKE 'Jen%' (prefix search) can use a B-tree index on the column because it is a range scan from "Jen" to "Jeo".
• ILIKE and indexes: standard B-tree indexes are case-sensitive and cannot support ILIKE. Use a citext column type or a functional index with lower() for case-insensitive prefix searches.

Reference: PostgreSQL documentation — Pattern Matching.