PostgreSQL regular expressions and full-text search

Beyond LIKE, PostgreSQL has POSIX regular expressions: ~ (matches), ~* (matches, case-insensitive), !~ (doesn’t match), plus regexp_replace, regexp_matches and regexp_split_to_table.

For searching real text, use full-text search. to_tsvector('english', text) breaks text into normalised words (“running” becomes “run”, stop-words like “the” vanish). to_tsquery or the friendlier websearch_to_tsquery builds a query, @@ tests a match, and ts_rank scores relevance. A GIN index on the tsvector makes it fast on millions of rows.

Syntax

WHERE col ~* 'pattern'
WHERE to_tsvector('english', body) @@ websearch_to_tsquery('english', 'words "exact phrase" -excluded')

Examples

Edit the code and run it. Output is printed the way psql prints it.

21.1Regex matching

1
2
3

SELECT name FROM products WHERE name ~ '^[A-Z][a-z]+ [A-Z]';
SELECT email FROM customers WHERE email ~* '\.(in|uk)$';
SELECT regexp_replace('Call +91 98765-43210 now', '[^0-9]', '', 'g') AS digits_only;

^ anchors the start, $ the end; [A-Z][a-z]+ is a capitalised word. The second pattern finds emails ending in .in or .uk (with .co.uk included). The last query strips everything except digits.

21.2What a tsvector looks like

1
2
3

SELECT to_tsvector('english', 'The quick brown foxes were running and jumping') AS vec,
       to_tsquery('english', 'run & fox') AS query,
       to_tsvector('english', 'The quick brown foxes were running') @@ to_tsquery('english', 'run & fox') AS matches;

Words are reduced to stems with their positions; “the” and “were” are dropped. The query run & fox matches because both stems are present.

21.3Searching product descriptions

SELECT name,
       ts_rank(to_tsvector('english', name || ' ' || description), q) AS rank
FROM products,
     websearch_to_tsquery('english', 'wireless OR usb -keyboard') AS q
WHERE to_tsvector('english', name || ' ' || description) @@ q
ORDER BY rank DESC, name;

websearch_to_tsquery understands search-box syntax: OR, quotes for phrases, and - to exclude. Results are ordered by relevance.

21.4Making it fast: a generated column and GIN index

ALTER TABLE products
  ADD COLUMN IF NOT EXISTS search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || coalesce(description, ''))) STORED;

CREATE INDEX IF NOT EXISTS products_search_idx ON products USING gin (search);

SELECT name FROM products WHERE search @@ websearch_to_tsquery('english', 'gaming');

A generated column keeps the tsvector up to date automatically whenever the row changes, and the GIN index lets PostgreSQL find matches without reading every row.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 21.1

Using full-text search on description, find the name of products that mention “battery”.

Hint

to_tsvector('english', description) @@ to_tsquery('english', 'battery')

Solution

SELECT name FROM products WHERE to_tsvector('english', description) @@ to_tsquery('english', 'battery');