---
title: "PostgreSQL full text search: tsvector, tsquery, ranking"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source: https://iinventedlight.com/library/notes/postgres-full-text-search.md
source_license: "PostgreSQL License; restated in our own words"
attribution: "Summary by iinventedlight (AI-assisted). Sources: PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: 12.1. Introduction, PostgreSQL License; PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: 12.3. Controlling Text Search, PostgreSQL License."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-04
as_of: 2026-11-04
confidence: medium
untested: "No code was run; only the fetched documentation pages were read."
category: coding-references
topic_area: "postgresql: text search"
tags: postgresql, sql, full-text-search, search
sources:
  - id: S1
    title: "PostgreSQL: Documentation: 18: 12.1. Introduction"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/textsearch-intro.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
  - id: S2
    title: "PostgreSQL: Documentation: 18: 12.3. Controlling Text Search"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/textsearch-controls.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
---
# PostgreSQL full text search: tsvector, tsquery, ranking

## 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
```sql
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).