liveBook · Manning

To_tsquery

As explained in Just Use Postgres!

to_tsquery is a PostgreSQL function that parses text into a tsquery value using PostgreSQL full-text search syntax and normalization rules.

to_tsquery parses text into a tsquery value for PostgreSQL full-text search. Use it when a search needs explicit Boolean logic, phrase matching, or proximity instead of requiring every generated lexeme. section 6.3.2

Build querytext with operators and grouping:

  • & requires both terms; | accepts either term; ! excludes a term or phrase; and <-> requires terms to be followed by one another. section 6.3.2
  • Group alternatives with parentheses, as in computer & animated & (lion | clownfish | donkey). section 6.3.2
  • Pass an optional regconfig argument when the search requires a full-text configuration other than the default. section 6.3.2

Combine alternatives in a movie search

SELECT id, name FROM omdb.movies WHERE lexemes @@ to_tsquery('computer & animated & (lion | clownfish | donkey)'); The query returns Finding Nemo, Shrek, and The Lion King. It requires computer and animated while accepting any one of the three animal terms. section 6.3.2

Exclude a phrase and require proximity

SELECT id, name FROM omdb.movies WHERE lexemes @@ to_tsquery('lion & !''The Lion King'''); The phrase is represented as !( 'lion' <-> 'king' ). For ordered proximity, use a distance operator such as return <3> king, which matches The Lord of the Rings: The Return of the King. section 6.3.2

to_tsquery([ config regconfig, ] querytext text) returns tsquery

Inspect the function signature while composing advanced tsquery expressions. section 6.3.2

How Just Use Postgres! covers it

How deep it goes
Defines operators, grouping, configuration, phrase handling, and distance matching without descending into parser internals.
Examples
Demonstrates animated-movie alternatives, lion exclusion, phrase parsing, and return-to-king proximity matching.
What it assumes
Assumes familiarity with PostgreSQL full-text search, tsvector values, and the @@ operator while contrasting plainto_tsquery behavior.
What you can do after
Apply it via PostgreSQL to construct executable full-text filters with Boolean logic, phrase exclusion, and term proximity.
How it connects
Connects tsquery expressions with tsvector values, full-text search, plainto_tsquery, and the @@ match operator.
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.