PostgreSQL Regular Expression Functions

A bare col ~ 'pattern' with no index causes a full sequential scan. For large tables with flexible pattern searches, a pg_trgm GIN index accelerates ~ queries by orders of magnitude. For structured full-text search, consider tsvector/tsquery instead.

✓ Instead: -- Sequential scan on large table (slow): SELECT * FROM articles WHERE body ~ 'postgres(ql)?'; -- Add trigram index first: CREATE INDEX ON articles USING GIN (body gin_trgm_ops); -- Now the same query uses the index.

regexp_count > 0 is semantically correct but wastes cycles counting every occurrence when you only care whether any match exists. Use regexp_like or the ~ operator for a boolean test — they short-circuit on the first match.

✓ Instead: -- Inefficient: counts all matches just to check existence SELECT * FROM t WHERE regexp_count(col, 'pattern') > 0; -- Efficient: short-circuits on first match SELECT * FROM t WHERE col ~ 'pattern';

regexp_like (and the ~ operator) are powerful but have regex compilation overhead. For simple starts-with, ends-with, or contains checks, LIKE or ILIKE is up to 10x faster and far more readable. Use regex only when the pattern genuinely requires it.

✓ Instead: -- Slow: regex for a simple prefix check SELECT * FROM t WHERE regexp_like(col, '^prefix'); -- Fast: LIKE SELECT * FROM t WHERE col LIKE 'prefix%';

regexp_matches returns SETOF text[], which means it acts as a set-returning function and can multiply rows in a SELECT list in unexpected ways. When you only want the first match, use regexp_match (PG 10+) instead — it returns a single text[] row or NULL.

✓ Instead: -- Avoid: regexp_matches without 'g' flag in a SELECT list SELECT id, (regexp_matches(col, 'pattern'))[1] FROM t; -- Prefer: regexp_match for single-match extraction SELECT id, (regexp_match(col, 'pattern'))[1] FROM t;

Without the 'g' flag, regexp_matches still returns SETOF text[] but only yields the first match. This set-returning behavior inside a SELECT list can cause unexpected row multiplication. On PostgreSQL 10+, use regexp_match (no 's') for cleaner single-match semantics.

✓ Instead: -- Avoid on PG 10+: set-returning function in SELECT list SELECT (regexp_matches(col, 'pattern'))[1] FROM t; -- Prefer: SELECT (regexp_match(col, 'pattern'))[1] FROM t;

Without the 'g' flag, regexp_replace only replaces the FIRST match. This is a common source of bugs when sanitizing strings (e.g., removing all special characters). Always add 'g' when you intend a global replace.

✓ Instead: -- Only removes the first non-digit character (bug): SELECT regexp_replace(phone, '[^0-9]', '', ''); -- Removes all non-digit characters (correct): SELECT regexp_replace(phone, '[^0-9]', '', 'g');

Chaining regexp_replace() calls to replace the 1st then 2nd occurrence is fragile and expensive — each pass rescans the whole string.

✓ Instead: Use regexp_replace(string, pattern, replacement, start, N) with the appropriate N to target the exact occurrence in one call.

regexp_split_to_array compiles a regex engine for every call. When the delimiter is a plain character (comma, pipe, tab), `string_to_array` is simpler and faster — there is no regex overhead.

✓ Instead: -- Unnecessary regex overhead for a fixed delimiter: SELECT regexp_split_to_array(col, ','); -- Prefer for fixed delimiters: SELECT string_to_array(col, ',');

Placing a set-returning function directly in the SELECT list (not in FROM/LATERAL) was deprecated in PostgreSQL 10 and produces confusing results. Always put regexp_split_to_table in the FROM clause.

✓ Instead: -- Deprecated / confusing: SELECT id, regexp_split_to_table(tags_csv, ',') FROM posts; -- Correct lateral usage: SELECT p.id, t.tag FROM posts p, regexp_split_to_table(p.tags_csv, ',') AS t(tag);

regexp_substr was introduced in PostgreSQL 15. On older versions this function does not exist. Use (regexp_match(str, pattern))[1] as a portable alternative for single-match extraction.

✓ Instead: -- PG 15+ only: SELECT regexp_substr(col, 'pattern'); -- Portable alternative (PG 10+): SELECT (regexp_match(col, 'pattern'))[1];

SIMILAR TO is a historical SQL standard compromise — it has confusing implicit anchoring, limited features compared to POSIX regex, and is not faster than the ~ operator. PostgreSQL internally rewrites SIMILAR TO as a regex anyway. Use LIKE/ILIKE for simple patterns and ~ for anything requiring alternation or quantifiers.

✓ Instead: -- SIMILAR TO: confusing and no performance benefit SELECT * FROM t WHERE col SIMILAR TO '(foo|bar)%'; -- Prefer POSIX regex for alternation: SELECT * FROM t WHERE col ~ '^(foo|bar)'; -- Or LIKE for simple prefix: SELECT * FROM t WHERE col LIKE 'foo%' OR col LIKE 'bar%';