Full-Text Search

Code

-- Create full-text search index
CREATE INDEX idx_articles_fts
ON articles USING gin(to_tsvector('english', title || ' ' || content));

-- Basic search
SELECT id, title, ts_rank(
    to_tsvector('english', title || ' ' || content),
    plainto_tsquery('english', 'database optimization')
) AS rank
FROM articles
WHERE to_tsvector('english', title || ' ' || content)
    @@ plainto_tsquery('english', 'database optimization')
ORDER BY rank DESC;

-- Highlight
SELECT ts_headline('english', content,
    plainto_tsquery('english', 'database'), 'MaxWords=20')
FROM articles;

-- Generated column
ALTER TABLE articles
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title,'') || ' ' || coalesce(content,''))
) STORED;

CREATE INDEX idx_search ON articles USING gin(search_vector);

Explanation

Full-text search uses tsvector and tsquery; GIN indexes accelerate search; ts_rank sorts.

More SQL Snippets