SQLearnAcademy
←Function reference
Full-text searchPostgreSQL only

TO_TSVECTOR

Prepares text for full-text search: splits it into words and reduces them to their stems.

Syntax

TO_TSVECTOR('<language config>', <text>)
<tsvector> @@ <tsquery>

Turns text into a tsvector — a set of word stems with their positions. A language configuration such as 'english' drops stop words (the, are) and reduces word forms to a stem: foxes becomes fox, running becomes run. You search it with the @@ operator together with TO_TSQUERY. Not every language has a built-in configuration — for those you use 'simple' or an extension.

Text into a set of stems
SELECT TO_TSVECTOR('english', 'The quick brown foxes are running') AS document;
document
'brown':3 'fox':4 'quick':2 'run':6

The numbers are word positions in the text. The stop words The and are are gone.

Checking for a match
SELECT TO_TSVECTOR('english', 'The foxes are running') @@ TO_TSQUERY('english', 'run') AS found;
found
true

The word run isn't in the text, but running reduces to the same stem.

The simple configuration — no stemming
SELECT TO_TSVECTOR('simple', 'The foxes are running') AS simple_config;
simple_config
'are':3 'foxes':2 'running':4 'the':1

'simple' only converts to lowercase: no word is dropped or shortened.

Watch out

Without a language configuration, the server setting is used, and the result may differ from server to server. Always specify the language explicitly.

Stop words disappear completely: with 'english', searching for the finds nothing, even where the word is present.

Calculating TO_TSVECTOR on the fly over a large table is slow. For fast search, a ready-made tsvector is stored in a column with a GIN index on it.

Learn more in the course

Full-text search