The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Simple pattern | WHERE title LIKE 'PostgreSQL%' | View examples |
| Case-insensitive pattern | WHERE title ILIKE '%database%' | View examples |
| Literal wildcard | WHERE code LIKE 'A\_%' ESCAPE '\' | View examples |
| POSIX regex match | WHERE value ~ '^[A-Z]{2}-[0-9]+$' | View examples |
| Case-insensitive regex | WHERE value ~* '^error:' | View examples |
| Parse a document | to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, '')) | View examples |
| Plain user query | plainto_tsquery('english', $1) | View examples |
| Web-style query | websearch_to_tsquery('english', $1) | View examples |
| Match document and query | search_vector @@ websearch_to_tsquery('english', $1) | View examples |
| Rank a match | ts_rank_cd(search_vector, query) AS rank | View examples |
| Highlight fragments | ts_headline('english', body, query) | View examples |
| Index a search vector | CREATE INDEX articles_search_idx
ON articles
USING GIN (search_vector); | View examples |
Use equality for exact identifiers, LIKE for simple anchored patterns, POSIX regular expressions for bounded structural matching, and full-text search for language-aware document retrieval. Treat user-supplied patterns as potentially expensive, choose text-search configuration explicitly, and index the expression queries actually use.
Step by step
Detailed examples
Use the simplest pattern language that fits
LIKE patterns use percent and underscore wildcards and match the whole string. A leading wildcard often prevents ordinary B-tree prefix use. POSIX regex is more expressive but hostile patterns can consume excessive resources; bound statement time and input length for untrusted search.
SELECT code, title
FROM articles
WHERE title ILIKE 'postgres%'
AND code ~ '^[A-Z]{2}-[0-9]+$'
ORDER BY code;Normalize documents with one explicit configuration
to_tsvector parses tokens, normalizes them to lexemes, removes stop words, and records positions. Concatenate nullable fields through coalesce. Store a generated vector or use an expression index, and use the same configuration in both document and query construction.
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B') AS search_vectorParse user input with a safe constructor
to_tsquery expects explicit operators and can reject malformed input. plainto_tsquery treats words as terms, phraseto_tsquery preserves phrase relationships, and websearch_to_tsquery accepts a user-friendly syntax and does not raise syntax errors for raw input. Parameterize the text regardless.
WITH input AS (
SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT article_id, title
FROM articles, input
WHERE search_vector @@ input.query;Rank after filtering and sanitize presentation
ts_rank and ts_rank_cd rank matches using frequencies, positions, weights, and normalization options; they do not define product relevance by themselves. ts_headline returns text containing markup-like delimiters, so escape source content or safely render configured markers rather than injecting it as trusted HTML.
WITH input AS (SELECT websearch_to_tsquery('english', $1) AS query)
SELECT title,
ts_rank_cd(search_vector, query) AS rank,
ts_headline('english', body, query, 'MaxWords=25, MinWords=10') AS snippet
FROM articles, input
WHERE search_vector @@ query
ORDER BY rank DESC, article_id;Keep indexed vectors synchronized
GIN is the preferred text-search index type for most workloads. A stored generated column keeps a vector aligned with its row; expression indexes avoid storage but require matching expressions. Indexes add write cost, and language configuration changes require rebuilding derived vectors and indexes.
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.