Full-Text Search

Code

-- Basic search
SELECT title, ts_rank_cd(tsv, q) AS rank
FROM articles, to_tsquery('english', 'postgres & tutorial') q
WHERE tsv @@ q
ORDER BY rank DESC;

-- Generate tsvector on the fly
SELECT title,
       to_tsvector('english', title || ' ' || body) AS tsv
FROM articles;

-- Combined index + weighted vector
ALTER TABLE articles ADD COLUMN tsv tsvector;
UPDATE articles SET tsv =
  setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
  setweight(to_tsvector('english', coalesce(body,'')),  'B');
CREATE INDEX idx_articles_tsv ON articles USING gin(tsv);

-- Phrase search with websearch_to_tsquery
SELECT * FROM articles
WHERE tsv @@ websearch_to_tsquery('english', '"postgres tutorial"');

Explanation

PostgreSQL full-text search converts text into tsvector tokens and matches them against tsquery expressions. setweight assigns priority (A, B, C, D) so title matches rank higher than body matches. A GIN index on the tsvector column makes searches fast even on large tables.

More PostgreSQL Snippets