PostgreSQL Full-Text Search - PostgreSQL Tutorial

LIKE '%word%' cannot use an index and doesn't understand language — it won't match "running" when you search "run". Postgres has real full-text search built in.

tsvector and tsquery

SQL

SELECT to_tsvector('english', 'The quick brown foxes are running');
-- 'brown':3 'fox':4 'quick':2 'run':6

SELECT to_tsvector('english', 'The quick brown foxes are running')
       @@ to_tsquery('english', 'fox & run');   -- true

Words are reduced to stems and stop words are dropped, so "foxes" matches "fox" and "running" matches "run".

Searching a table

SQL

SELECT title
FROM books
WHERE to_tsvector('english', title || ' ' || COALESCE(description, ''))
      @@ plainto_tsquery('english', 'nepali literature');

Query builders

Function	Input style
`to_tsquery`	explicit operators: `cat & dog`
`plainto_tsquery`	plain words, all ANDed
`phraseto_tsquery`	words in order
`websearch_to_tsquery`	Google-like: quotes, `or`, `-word`

websearch_to_tsquery is the right choice for a user-facing search box — it never errors on odd input, unlike to_tsquery.

SQL

SELECT * FROM books
WHERE search_vector @@ websearch_to_tsquery('english', '"palpasa cafe" -old');

A generated, indexed search column

The practical setup — Postgres maintains it for you:

SQL

ALTER TABLE books ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(author, '')), 'B') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'C')
) STORED;

CREATE INDEX idx_books_search ON books USING GIN (search_vector);

setweight marks which field a word came from, so title matches can outrank description matches.

Ranking results

SQL

SELECT
    title,
    ts_rank(search_vector, query) AS rank
FROM books, websearch_to_tsquery('english', 'nepali novel') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

Highlighting matches

SQL

SELECT ts_headline('english', description,
                   websearch_to_tsquery('english', 'nepali'))
FROM books;

Fuzzy matching for typos

Full-text search won't catch misspellings. Add trigram similarity:

SQL

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_books_title_trgm ON books USING GIN (title gin_trgm_ops);

SELECT title, similarity(title, 'palpsa cafe') AS sim
FROM books
WHERE title % 'palpsa cafe'
ORDER BY sim DESC;

pg_trgm also makes ordinary ILIKE '%...%' queries indexable — often the quickest win for an autocomplete box.