Skip to main content

Edit this page

TSQUERY

TSQUERY is the type of a full-text query expression evaluated against an inverted index with the @@ operator. It is the right-hand operand of every full-text predicate: column @@ tsquery.

Producing a TSQUERY​

A TSQUERY is rarely written as a literal. You normally build one of three ways:

  • A bare string, analyzed by the column's text search dictionary. Multi-token input matches any token (OR semantics).
  • A constructor function such as ts_phrase, ts_levenshtein, ts_between or to_tsquery. See the function reference.
  • A cast that changes how a string is interpreted: 'text'::tokenize('dictionary') analyzes with a named dictionary ('keyword' for an exact, un-analyzed token), and query::boost(factor) scales its score contribution.
Query
SELECT id, body FROM notes_idx WHERE body @@ ts_phrase('quick') ORDER BY id;
Result
 id | body----+-----------------  1 | quick brown fox  3 | quick red fox

Composing queries​

TSQUERY values compose with a small set of operators, so a complex search is assembled from simple parts. They fall into three groups:

GroupOperators
Boolean|| (OR), && (AND), !! (NOT)
Proximity## (phrase / gap)
Boost^ (score weight)

A string operand is analyzed by the column's dictionary before it is matched; the ::tokenize cast overrides that analysis.

Boolean operators​

|| matches either sub-query:

Query
SELECT id, body FROM notes_idxWHERE body @@ (ts_phrase('brown fox') || ts_phrase('lazy dog'))ORDER BY id;
Result
 id | body----+-----------------  1 | quick brown fox  2 | lazy dog sleeps

&& requires both sub-queries:

Query
SELECT id, body FROM notes_idxWHERE body @@ (ts_phrase('quick') && ts_phrase('fox'))ORDER BY id;
Result
 id | body----+-----------------  1 | quick brown fox  3 | quick red fox

!! is unary NOT; combine it with && to exclude matches — here "quick but not brown":

Query
SELECT id, body FROM notes_idxWHERE body @@ (ts_phrase('quick') && !!ts_phrase('brown'))ORDER BY id;
Result
 id | body----+---------------  3 | quick red fox

Proximity​

## requires the operands as an adjacent phrase. a ## N ## b allows an exact N-token gap and a ## [min, max] ## b a gap range:

Query
SELECT id, body FROM notes_idx WHERE body @@ ('brown' ## 'fox') ORDER BY id;
Result
 id | body----+-----------------  1 | quick brown fox

Boost​

^ scales a sub-query's contribution to the relevance score without changing which rows match — here doubling the weight of the quick clause:

Query
SELECT id, body FROM notes_idxWHERE body @@ (ts_phrase('fox') || ts_phrase('quick') ^ 2.0)ORDER BY id;
Result
 id | body----+-----------------  1 | quick brown fox  3 | quick red fox

Controlling analysis​

A string operand is normally analyzed by the column's dictionary. The ::tokenize cast overrides that — ::tokenize('keyword') forces an exact, un-analyzed token match:

Query
SELECT id, body FROM notes_idx WHERE body @@ 'fox'::tokenize('keyword') ORDER BY id;
Result
 id | body----+-----------------  1 | quick brown fox  3 | quick red fox

Relationship to VARCHAR​

TSQUERY is reinterpret-compatible with VARCHAR, so a string flows into a TSQUERY position automatically (it is then analyzed as described above). A TSQUERY only has meaning inside an @@ predicate against an indexed column; it is not a general-purpose stored type.

See also​