skip to content

In PostgreSQL, how do ILIKE, ~ and ~* differ from LIKE when you search stock symbols or company names?

level: juniorimportance: must knowfreq 58%

answer

  1. whole string versus anywhere
  2. case-insensitive variants
  3. POSIX regex operators
  4. ~~ shows up in EXPLAIN
  5. SIMILAR TO is a third dialect

basics

~20 s

ILIKE is a case-insensitive LIKE that must match the whole string with % and _ wildcards; ~ and ~* are POSIX regular-expression matches, case-sensitive and case-insensitive, that match anywhere unless anchored with ^ and $.

solid answer

~40 s

`LIKE` and PostgreSQL's `ILIKE` use `%` and `_` wildcards and must match the **entire** string; `ILIKE` ignores case according to the active locale and is not standard SQL. The parser turns them into the operators `~~` and `~~*`, which is what `EXPLAIN` shows. `~` and `~*` match a **POSIX regular expression** case-sensitively and case-insensitively, and a regex matches **anywhere** in the string unless you anchor it, so `name ~ 'ACME'` finds `NYSE:ACME`. `!~` and `!~*` negate them. `SIMILAR TO` is the SQL-standard hybrid: whole-string like `LIKE`, with `|`, `*`, `+` and groups, but `.` is literal. None of them supports nondeterministic collations, and an unanchored pattern cannot use a plain B-tree index, so large searches need a dedicated index type.

code

sql · 5 lines
sql
SELECT symbol, company_name
FROM listings
WHERE company_name ILIKE '%acme%'
   OR symbol ~ '^ACM[A-Z]?$'
   OR company_name ~* '\mholdings\M';

go deeper

for a junior

Recall that ILIKE is case-insensitive LIKE, that ~ and ~* are regex matches, and that a regex matches anywhere unless anchored.

for a middle

Explain the operator forms ~~ and ~~*, how SIMILAR TO differs from both LIKE and POSIX regex, and why the period is literal in SIMILAR TO.

for a senior

Show production judgment: pattern predicates scan without a suitable index, and untrusted regexes need escaping or a time cap.

for a principal

Choose a search strategy for user-facing lookup: pattern operators for small or anchored cases, a dedicated text index or search feature when patterns are unanchored and data is large.

## Four families of pattern matching PostgreSQL offers three pattern languages and a case-insensitive form of the simplest one. In a stock-ticker application they answer questions such as "find every listing whose name contains Acme" or "find symbols that are four capital letters". | Predicate | Pattern language | Must match | Case | Operator form | |---|---|---|---|---| | `LIKE` | `%` any string, `_` one character | Whole string | Sensitive | `~~`, `!~~` | | `ILIKE` | Same as `LIKE` | Whole string | Insensitive (active locale) | `~~*`, `!~~*` | | `SIMILAR TO` | SQL-standard regex | Whole string | Sensitive | Rewritten to `~` | | `~` / `~*` | POSIX regular expression | Anywhere unless anchored | Sensitive / insensitive | `!~`, `!~*` negate | ## LIKE and ILIKE `LIKE` is standard SQL and its wildcards are the language topic's subject. `ILIKE` is a **PostgreSQL extension**: the same pattern, matched without regard to case according to the current locale. - `company_name ILIKE '%acme%'` finds `Acme Corp`, `ACME Holdings` and `The acme fund`. - The parser rewrites `LIKE` to `~~` and `ILIKE` to `~~*`; you see those names in `EXPLAIN` and can use them with `ANY (array)`. - `ILIKE` does **not** support nondeterministic collations. - For a simple prefix test, the `^@` operator and `starts_with()` exist too. ## POSIX regular expressions with ~ and ~* PostgreSQL's regex engine implements **advanced regular expressions**: character classes, alternation, quantifiers, groups and shorthand escapes such as `\d` (digit) and `\s` (whitespace). - `~` is case-sensitive; `~*` is case-insensitive; `!~` and `!~*` return true when the pattern does **not** match. - A regex matches **anywhere** in the string: `'NYSE:ACME' ~ 'ACME'` is true. Anchor with `^` and `$` for a whole-string test: `symbol ~ '^[A-Z]{1,5}$'`. - The function `regexp_like(string, pattern, flags)` gives the same result as `~`, or as `~*` with the `i` flag. ## SIMILAR TO `SIMILAR TO` is the SQL standard's regular expression, a cross between `LIKE` and POSIX: 1. Like `LIKE`, it must match the **entire string**, and `%` and `_` are its wildcards. 2. It borrows `|`, `*`, `+`, `?`, `{m,n}`, parentheses and bracket classes from POSIX. 3. The period `.` is **not** a metacharacter, so `'ACMEXB' SIMILAR TO 'ACME.B'` is false. PostgreSQL's parser converts a `SIMILAR TO` pattern into a POSIX regex with `similar_to_escape()` and evaluates it with `~`, so it has no performance advantage over writing the regex yourself. ## Practical cautions - **Indexes.** A plain B-tree can serve only left-anchored patterns, and only with a suitable operator class or the C collation; `ILIKE '%acme%'` and unanchored regexes need a trigram index. Both are other topics; the interview point is that these predicates otherwise scan the table. - **Case folding.** For case-insensitive equality on a column, a case-insensitive collation or `citext` may fit better than `ILIKE`; that belongs to the collations topic. - **Hostile patterns.** The manual warns that regular expressions, and `SIMILAR TO`, can be contrived to take arbitrary time and memory. Never pass a user's search text as a regex without escaping it or capping run time with a statement timeout; `LIKE` is safer for untrusted patterns. - **NULL.** Every one of these predicates returns NULL when either side is NULL, so `NOT (name ~ 'x')` does not return rows with a NULL name. ## Choosing among them A practical order of preference for the ticker application: 1. **Exact or prefix lookups** (`symbol = 'ACME'`, `symbol ^@ 'AC'`) when the user types a symbol; these are the cheapest and easiest to index. 2. **`ILIKE` with wildcards** for a simple "contains" search on company names, where users expect case not to matter. 3. **`~` or `~*`** when the rule is structural: a symbol format, a word boundary, alternatives such as `'(Corp|Inc|Ltd)$'`. 4. **`SIMILAR TO`** mainly when portable, standard SQL matters more than expressiveness; most PostgreSQL code uses POSIX regexes instead. Whatever the choice, test the edge cases: mixed case, punctuation in names, NULLs, and strings that contain the pattern only as a substring. In an interview, the strong answer contrasts whole-string and anywhere matching, names the case-insensitive forms, and mentions that `SIMILAR TO` treats `.` literally.

  • Why does EXPLAIN show ~~* when the query says ILIKE?
    PostgreSQL's parser translates `LIKE`, `ILIKE`, `NOT LIKE` and `NOT ILIKE` into the operators `~~`, `~~*`, `!~~` and `!~~*`. Plans, error messages and operator-class definitions use those names, and you can write them directly, for example with `ANY (array)`.
  • How do you test that a whole symbol is one to five capital letters?
    Anchor a regex at both ends: `symbol ~ '^[A-Z]{1,5}$'`. Without `^` and `$`, `~` succeeds on any string containing such a run, such as `abcDEF`. The SQL-standard form `symbol SIMILAR TO '[A-Z]{1,5}'` is already whole-string.

  • The ~ operator matches only when the pattern covers the whole string, like LIKE.
  • ILIKE is standard SQL and works the same in every database.
  • In SIMILAR TO the period matches any single character, as in POSIX.
  • SIMILAR TO is faster than ~ because it is a simpler SQL-standard matcher.
  • Passing user text straight to ~ is harmless because it cannot run SQL.