0Pricing
SQL Academy · Lesson

Full-Text Search Introduction (tsvector, to_tsquery)

Index documents with tsvector, run natural-language queries with to_tsquery, and rank results with ts_rank.

Full-Text Search Introduction (tsvector, to_tsquery) is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Full-Text Search?

LIKE/ILIKE finds substrings. Full-text search (FTS) understands words: stemming, stop words, language rules, ranked relevance. Built into PostgreSQL — no extension needed.

Two Core Types

FTS rests on two types:

  • tsvector — a parsed document: lexemes + positions
  • tsquery — a parsed query: lexemes + boolean operators

You match them with the @@ operator.

Parsing a Document

Turn raw text into a tsvector:

SELECT to_tsvector('english', 'The quick brown foxes jump over the lazy dogs');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
-- Stop words ('the', 'over') dropped; words stemmed (foxes → fox)

Parsing a Query

And the search input:

SELECT to_tsquery('english', 'foxes & dogs');
-- 'fox' & 'dog'

Matching

Use @@ to test:

SELECT to_tsvector('english', 'The lazy fox')
        @@ to_tsquery('english', 'fox');    -- TRUE

Indexing a Documents Table

Add a tsvector column and a GIN index for fast queries:

CREATE TABLE articles (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body  TEXT NOT NULL
);

-- Either keep a generated tsvector column:
ALTER TABLE articles
  ADD COLUMN search_doc tsvector
    GENERATED ALWAYS AS (
      to_tsvector('english',
        coalesce(title,'') || ' ' || coalesce(body,'')
      )
    ) STORED;

CREATE INDEX articles_search_idx ON articles USING GIN (search_doc);

Searching

With the index, queries are fast:

SELECT id, title
FROM articles
WHERE search_doc @@ to_tsquery('english', 'postgres & json')
ORDER BY id DESC;

Boolean Operators in Queries

tsquery supports & (AND), | (OR), ! (NOT), and grouping:

to_tsquery('english', '(cat | dog) & !rabbit');

Phrase Search

Use <-> for adjacent words:

to_tsquery('english', 'postgres <-> performance');
-- finds 'postgres performance', not 'postgres is great for performance'

Friendly Web Queries: websearch_to_tsquery

Accept Google-style queries:

SELECT websearch_to_tsquery('english', 'postgres "json indexing" -mongo');

Ranking Results

Rank with ts_rank or ts_rank_cd:

SELECT id, title,
       ts_rank(search_doc, query) AS rank
FROM articles, to_tsquery('english', 'postgres & json') AS query
WHERE search_doc @@ query
ORDER BY rank DESC
LIMIT 20;

Highlighting

Show matched snippets:

SELECT ts_headline('english', body,
  to_tsquery('english', 'postgres & json'),
  'StartSel=<mark>,StopSel=</mark>')
FROM articles
WHERE search_doc @@ to_tsquery('english', 'postgres & json');

Recap

FTS is built into PostgreSQL.

  • tsvector + tsquery + @@
  • GIN index for speed
  • websearch_to_tsquery for human input
  • ts_rank for relevance ordering

Quick Check

Which operator matches a tsvector against a tsquery?

Frequently asked questions

Is the “Full-Text Search Introduction (tsvector, to_tsquery)” lesson free?

Yes — the full text of “Full-Text Search Introduction (tsvector, to_tsquery)” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Full-Text Search Introduction (tsvector, to_tsquery)”?

Index documents with tsvector, run natural-language queries with to_tsquery, and rank results with ts_rank. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Full-Text Search Introduction (tsvector, to_tsquery)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Concatenation: || and CONCAT
  2. UPPER, LOWER, SUBSTRING, REPLACE
  3. TRIM, LPAD, RPAD
  4. Full-Text Search Introduction (tsvector, to_tsquery)
← Back to SQL Academy