liveBook · Manning

To_tsvector function

As explained in Just Use Postgres!

The PostgreSQL to_tsvector function converts text into a tsvector using a specified or default text-search configuration.

Converts application text into a tsvector containing normalized lexemes and their original positions. Pass a text-search configuration explicitly or let PostgreSQL use default_text_search_config, which is english in this setup. section 6.2.1

Use the generated lexemes in one of three ways:

  • Generate them during every search query, then discard them; this avoids storage but repeats processing. section 6.2.2
  • Store them in a tsvector column and index that column with GIN or GiST for faster searches. section 6.2.2
  • Build a GIN or GiST index directly from generated lexemes, avoiding a stored copy when the configuration can be specified. section 6.2.2

Convert a movie description

SELECT * FROM to_tsvector( 'The explorers must save the fragile peace between Earth and the aliens.'); The result includes lexemes such as 'explor', 'fragil', and 'alien', while removing stop words. Positions remain attached: 'explor':2 and 'alien':12. Concatenate a title and description when both fields should be searchable.

Store searchable movie text

ALTER TABLE omdb.movies ADD COLUMN lexemes tsvector GENERATED ALWAYS AS ( to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, '')) ) STORED; This keeps title and description lexemes available in a generated column for later indexing and querying.

How Just Use Postgres! covers it

How deep it goes
Explains configuration selection, normalization, stop-word removal, positional metadata, and storage strategies without detailing parser internals.
Examples
Demonstrates movie-description conversion, concatenated title and description input, and a stored generated lexeme column.
What it assumes
Assumes text input and a PostgreSQL full-text search configuration, while defining default selection and lexeme generation.
What you can do after
Apply via PostgreSQL SQL to generate searchable lexemes, combine text fields, and store results in a tsvector column.
How it connects
Connects text-search configuration, lexemes, stop words, tsvector, positional operators, and GIN or GiST indexes.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.