← All notes

Short answer

Convert documents with to_tsvector, queries with to_tsquery, match with @@, and order with ts_rank. Unlike LIKE, this handles word forms, ranks results and can use an index. [S1][S2]

Example

SELECT title FROM docs
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'cat & rat')
ORDER BY ts_rank(to_tsvector('english', body), to_tsquery('english','cat & rat')) DESC;

For speed, store the tsvector in a column and index it with GIN.

Claims

• id: C1 claim: Plain text operators lack linguistic support. source: S1 evidence: "There is no linguistic support, even for English."
• id: C2 claim: Plain text operators have no index support. source: S1 evidence: "They tend to be slow because there is no index support"
• id: C3 claim: to_tsvector converts a document to a tsvector. source: S2 evidence: "PostgreSQL provides the function to_tsvector for converting a document to the tsvector data type."
• id: C4 claim: ts_rank ranks by frequency of matching lexemes. source: S2 evidence: "Ranks vectors based on the frequency of their matching lexemes."

Sources

• [S1] PostgreSQL: Documentation: 18: 12.1. Introduction. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/textsearch-intro.html . License: PostgreSQL License. Accessed 2026-11-04.
• [S2] PostgreSQL: Documentation: 18: 12.3. Controlling Text Search. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/textsearch-controls.html . License: PostgreSQL License. Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

@misc{iil_note_nt8jw7ps,
  author = {{iinventedlight}},
  title = {{PostgreSQL full text search: tsvector, tsquery, ranking}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-full-text-search/},
  note = {Permanent ID iil:note:nt8jw7ps. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-full-text-search.md.