PostgreSQL Full-Text Search (to_tsvector and to_tsquery)

PostgreSQL's full-text search uses to_tsvector() to convert documents into searchable tokens and to_tsquery() to create search queries. Together with the @@ match operator, they provide powerful, language-aware text searching with stemming, ranking, and phrase matching.

Syntax

-- Convert text to tsvector (searchable document)
to_tsvector([config,] document)

-- Convert text to tsquery (search query)
to_tsquery([config,] query)
plainto_tsquery([config,] query)      -- Plain text query
phraseto_tsquery([config,] query)     -- Phrase query
websearch_to_tsquery([config,] query) -- Web search syntax

-- Match operator
tsvector @@ tsquery

-- Common configs: 'english', 'simple', 'spanish', 'french', etc.

Official Documentation

Example usage

-- Basic full-text search
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || content)
      @@ to_tsquery('english', 'postgresql & database');

-- Create tsvector column for better performance
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);

-- Create GIN index for fast searching
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);

-- Search with index
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql');

-- Plain text query (easier syntax)
SELECT * FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgresql database');

-- Phrase search
SELECT * FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'full text search');

-- Web search syntax (Google-like)
SELECT * FROM articles
WHERE search_vector @@ websearch_to_tsquery('english', 'postgresql OR mysql -oracle');

-- Boolean operators in tsquery
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & (performance | optimization)');
-- Matches: "postgresql AND (performance OR optimization)"

-- NOT operator
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & !oracle');
-- Matches "database" but NOT "oracle"

-- Ranking results by relevance
SELECT
    title,
    ts_rank(search_vector, query) AS rank
FROM articles,
     to_tsquery('english', 'postgresql & database') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- ts_rank_cd for cover density ranking
SELECT
    title,
    ts_rank_cd(search_vector, query) AS rank
FROM articles,
     to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Highlight search matches
SELECT
    title,
    ts_headline('english', content, query) AS snippet
FROM articles,
     to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query;

-- Multiple language support
SELECT * FROM documents
WHERE to_tsvector('spanish', content) @@ to_tsquery('spanish', 'buscar');

-- Search with weights (A > B > C > D)
UPDATE articles
SET search_vector =
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', content), 'B');

-- Maintain tsvector automatically with trigger
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);

-- Combine with regular filters
SELECT * FROM products
WHERE search_vector @@ to_tsquery('english', 'laptop')
  AND price < 1000
  AND in_stock = true
ORDER BY ts_rank(search_vector, to_tsquery('english', 'laptop')) DESC;

-- Prefix matching
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgre:*');
-- Matches: postgres, postgresql, etc.

-- Custom configuration
SELECT * FROM articles
WHERE to_tsvector('simple', content) @@ to_tsquery('simple', 'PostgreSQL');
-- 'simple' config: no stemming, exact matching

Common issues

1. Language config: Always specify language config for consistent stemming.
2. Performance: Without GIN index, full-text search on large tables is very slow.
3. Query syntax: to_tsquery requires special syntax (&, |, !). Use plainto_tsquery for plain text.
4. Stemming: "running" matches "run" due to stemming. Use 'simple' config for exact matching.

Best practices

1. Store tsvector in dedicated column and create GIN index for performance.
2. Use triggers to automatically update tsvector columns when data changes.
3. Use plainto_tsquery or websearch_to_tsquery for user-facing search (easier syntax).
4. Combine with ts_rank for relevance-based ordering.
5. Use ts_headline to show search context in results.

Frequently Asked Questions

Q: What's the difference between to_tsquery and plainto_tsquery?
A: to_tsquery requires special syntax with & (AND), | (OR), ! (NOT). plainto_tsquery accepts plain text and automatically converts it: plainto_tsquery('postgresql database') becomes 'postgresql' & 'database'.

Q: Do I need to create a separate tsvector column?
A: Not required, but highly recommended for performance. Computing to_tsvector() on every search is slow. Store it in a column with a GIN index for fast searches.

Q: How does stemming work?
A: Stemming reduces words to their root form. "running", "runs", "ran" all stem to "run". Searching for "run" matches all variations. Use 'english' config for English stemming, 'simple' for no stemming.

Q: Can I search across multiple columns?
A: Yes, concatenate them: to_tsvector('english', title || ' ' || content || ' ' || tags). For different importance, use setweight: setweight(to_tsvector(title), 'A') || setweight(to_tsvector(content), 'B').

Q: How do I implement autocomplete/search suggestions?
A: Use prefix matching with :*: to_tsquery('postgre:*') matches "postgres", "postgresql", etc. Combine with pg_trgm extension for better fuzzy matching.