Skip to main content
SereneDBSereneDB
897Contact Us

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 string whose whole text is a query expression runs as that expression instead; see Query text in a string.
  • 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.

Further modifiers ride on a TSQUERY value the same way: ::slop(N) widens a phrase, ::score(scorer) scores one subtree with a scorer of its own (::score(NULL) excludes it from scoring) and ::merge(policy) sets how a boolean node combines its branches. Because they attach to the value rather than to the predicate, they survive being passed through a constructor such as ts_any or bound as a statement parameter. See Per-node score control. ::min_match(K) rides along too: it requires K of the value's alternatives (its words, its || branches or its ts_any elements), and on an OR of predicates it requires K of the branches. See Matching K of N predicates.

SQL
SELECT id, body FROM notes_idx WHERE body @@ ts_phrase('quick') ORDER BY id;
 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:

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

&& requires both sub-queries:

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

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

SQL
SELECT id, body FROM notes_idxWHERE body @@ (ts_phrase('quick') && !!ts_phrase('brown'))ORDER BY id;
 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:

SQL
SELECT id, body FROM notes_idx WHERE body @@ ('brown' ## 'fox') ORDER BY id;
 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:

SQL
SELECT id, body FROM notes_idxWHERE body @@ (ts_phrase('fox') || ts_phrase('quick') ^ 2.0)ORDER BY id;
 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:

SQL
SELECT id, body FROM notes_idx WHERE body @@ 'fox'::tokenize('keyword') ORDER BY id;
 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, unless its text is a query expression). A TSQUERY only has meaning inside an @@ predicate against an indexed column; it is not a general-purpose stored type.

Query text in a string​

A string on the right of @@, whether a literal or a bound parameter, runs as a query when its whole text is a query expression. Such text is made only of full-text constructor calls such as ts_phrase, ts_like, ts_any or to_tsquery, the ||, &&, !!, ## and ^ operators, casts to TSQUERY or to its modifiers (::tokenize, ::boost, ::slop, ::score, ::merge, ::min_match), and constants. So body @@ 'ts_like(''qu%'')' is a LIKE match, not a search for the words ts_like and qu. Any other text is analyzed as words and never executed: text that names another function, a column or a subquery, and text such as 2024-10-05 whose value is not a TSQUERY.

This is also the text a TSQUERY value with modifiers reads back as, through the VARCHAR cast or over the wire: ('fox red'::TSQUERY)::min_match(2) reads back as 'fox red'::min_match(2), and that text sent as a parameter is the same query again, threshold included. Modifier values in the text are checked as they are in SQL, so a parameter whose text is 'lazy'::boost(-1) fails with boost() factor must be >= 0, got -1 instead of being searched as words.

Below, the string runs as two phrases, so quick red fox does not match although it has fox, and the parameter's text carries a threshold of all three words:

SQL
SELECT id, body FROM notes_idxWHERE body @@ 'ts_phrase(''brown fox'') || ts_phrase(''lazy dog'')'ORDER BY id;
PREPARE notes_search AS SELECT id, body FROM notes_idx WHERE body @@ $1::TSQUERY ORDER BY id;
EXECUTE notes_search('''quick red fox''::min_match(3)');
DEALLOCATE notes_search;
 id | body
----+-----------------
  1 | quick brown fox
  2 | lazy dog sleeps

 id | body
----+---------------
  3 | quick red fox

# DOCS_TEST_END

See also​

Edit this page ↗