tsquery is PostgreSQL’s normalized representation of a full-text search query. It converts application text into lexemes, removes stop words, and combines terms with search operators so a tsvector can be tested with @@. section 6.3
Choose the query-construction function according to how much control the search needs:
- Use plainto_tsquery for ordinary user-entered phrases. It normalizes words, removes stop words, and inserts & between the remaining lexemes. section 6.3.1
- Use to_tsquery for explicit filtering with &, |, !, and <->, including grouped expressions and phrase-like constraints. section 6.3.2
- Match a generated tsvector against the resulting tsquery with @@; PostgreSQL returns true when the document satisfies the query. section 6.3
Normalize a user phrase
SELECT plainto_tsquery('a computer animated film'); -- 'comput' & 'anim' & 'film' SELECT id, name FROM omdb.movies WHERE lexemes @@ plainto_tsquery('a computer animated film'); The first expression removes the stop word “a” and reduces the remaining words to lexemes; the second returns matching movies.
Add advanced filters
SELECT id, name FROM omdb.movies WHERE lexemes @@ to_tsquery('computer & animated & (lion | clownfish | donkey)'); SELECT id, name FROM omdb.movies WHERE lexemes @@ to_tsquery('lion & !''The Lion King'''); These queries require computer and animated together, then allow one of three alternatives; the second excludes the named phrase.
A tsquery can also drive relevance ranking. ts_rank considers term frequency and position, while ts_rank_cd additionally considers term density; weighted tsvectors can prioritize fields such as titles. section 6.4
