Full-Text Search Functions

The @@ match operator and the TSQUERY constructors, operators and PostgreSQL-compatible parsers used to build full-text queries against an inverted index. Every example below shares the dataset created in Setup; see Full-Text Search for a task-oriented guide.

Setup

The examples on this page share one dataset. Expand to see the schema and sample data.

SQL

CREATE TABLE docs (id INTEGER PRIMARY KEY, body VARCHAR, category VARCHAR, dual VARCHAR);
CREATE TEXT SEARCH DICTIONARY en AS    split_text(case := 'lower') | normalize_tokens('en_US.UTF-8', accent := false)    WITH (frequency, position, offset);
CREATE INDEX docs_idx ON docs USING inverted (id, body en, category, dual en) INCLUDE (dual);
INSERT INTO docs VALUES    (1, 'quick brown fox jumps over lazy dog', 'sci-fi', 'x'),    (2, 'quick red fox jumps over lazy dog', 'sci-fi', NULL),    (3, 'slow grey turtle sleeps', 'drama', 'y'),    (4, 'something else entirely', 'comedy', 'z');
VACUUM (REFRESH_TABLE) docs;
CREATE TABLE titles (id INTEGER PRIMARY KEY, title VARCHAR);
CREATE TEXT SEARCH DICTIONARY bigram AS generate_ngrams(2, 2) WITH (frequency, position);
CREATE INDEX titles_idx ON titles USING inverted (id, title bigram);
INSERT INTO titles VALUES (1, 'hello'), (2, 'help'), (3, 'world'), (4, 'held');
VACUUM (REFRESH_TABLE) titles;
CREATE TABLE vecs (id INTEGER, emb FLOAT[3]);
CREATE INDEX vecs_l2 ON vecs USING inverted (id, emb ivf (metric = 'l2'));
INSERT INTO vecs VALUES (1, [1, 0, 0]::FLOAT[3]), (2, [0, 5, 0]::FLOAT[3]), (3, [0, 0, 1]::FLOAT[3]);
VACUUM (REFRESH_TABLE) vecs;
CREATE TABLE vecs_c (id INTEGER, emb FLOAT[3]);
CREATE INDEX vecs_cos ON vecs_c USING inverted (id, emb ivf (metric = 'cosine'));
INSERT INTO vecs_c VALUES (1, [1, 0, 0]::FLOAT[3]), (2, [0, 1, 0]::FLOAT[3]), (3, [1, 1, 0]::FLOAT[3]);
VACUUM (REFRESH_TABLE) vecs_c;
CREATE TABLE vecs_l (id INTEGER, emb FLOAT[3]);
CREATE INDEX vecs_l1 ON vecs_l USING inverted (id, emb ivf (metric = 'l1'));
INSERT INTO vecs_l VALUES (1, [1, 0, 0]::FLOAT[3]), (2, [0, 5, 0]::FLOAT[3]), (3, [0, 0, 1]::FLOAT[3]);
VACUUM (REFRESH_TABLE) vecs_l;
CREATE TABLE vecs_i (id INTEGER, emb FLOAT[3]);
CREATE INDEX vecs_ip ON vecs_i USING inverted (id, emb ivf (metric = 'ip'));
INSERT INTO vecs_i VALUES (1, [1, 0, 0]::FLOAT[3]), (2, [0, 5, 0]::FLOAT[3]), (3, [2, 2, 2]::FLOAT[3]);
VACUUM (REFRESH_TABLE) vecs_i;
CREATE TABLE geo (id INTEGER PRIMARY KEY, geo GEOMETRY('OGC:CRS84'));
CREATE TEXT SEARCH DICTIONARY gj AS encode_geojson(coding := 's2point');
CREATE INDEX geo_idx ON geo USING inverted (id, geo gj);
INSERT INTO geo VALUES    (1, 'POINT(37.6 55.7)'::GEOMETRY('OGC:CRS84')),    (2, 'POINT(37.7 55.8)'::GEOMETRY('OGC:CRS84'));
VACUUM (REFRESH_TABLE) geo;
CREATE TABLE scored (id INTEGER PRIMARY KEY, body VARCHAR);
CREATE TEXT SEARCH DICTIONARY scored_en AS    split_text(case := 'lower') | normalize_tokens('en_US.UTF-8', accent := false)    WITH (frequency, position, norm);
CREATE INDEX scored_idx ON scored USING inverted (id, body scored_en);
INSERT INTO scored VALUES    (1, 'the quick brown fox'),    (2, 'fox fox fox in the henhouse'),    (3, 'a clever fox outwits two hounds in the meadow'),    (4, 'quick brown dog runs'),    (5, 'the lazy dog sleeps all day');
VACUUM (REFRESH_TABLE) scored;
CREATE TABLE geo_demo (id INTEGER PRIMARY KEY, name VARCHAR, shape JSON);
CREATE TEXT SEARCH DICTIONARY geo_demo_dict AS encode_geojson();
CREATE INDEX geo_demo_idx ON geo_demo USING inverted (id, name, shape geo_demo_dict);
INSERT INTO geo_demo VALUES    (1, 'red_square',  '{"type":"Polygon","coordinates":[[[0,0],[10,0],[10,10],[0,10],[0,0]]]}'),    (2, 'inner_point', '{"type":"Point","coordinates":[5,5]}'),    (3, 'edge_point',  '{"type":"Point","coordinates":[10,5]}'),    (4, 'far_point',   '{"type":"Point","coordinates":[50,50]}'),    (5, 'overlap_box', '{"type":"Polygon","coordinates":[[[5,5],[15,5],[15,15],[5,15],[5,5]]]}');
VACUUM (REFRESH_TABLE) geo_demo;
CREATE TABLE cities (id INTEGER PRIMARY KEY, name VARCHAR, loc GEOMETRY('OGC:CRS84'));
CREATE TEXT SEARCH DICTIONARY cities_dict AS encode_geojson(coding := 's2point');
CREATE INDEX cities_idx ON cities USING inverted (id, name, loc cities_dict);
INSERT INTO cities VALUES    (1, 'kremlin',    'POINT(37.617499 55.752023)'::GEOMETRY('OGC:CRS84')),    (2, 'red_square', 'POINT(37.620795 55.753930)'::GEOMETRY('OGC:CRS84')),    (3, 'gorky_park', 'POINT(37.601111 55.731389)'::GEOMETRY('OGC:CRS84')),    (4, 'spb_palace', 'POINT(30.314611 59.939095)'::GEOMETRY('OGC:CRS84'));
VACUUM (REFRESH_TABLE) cities;
CREATE TABLE passages (id INTEGER PRIMARY KEY, body VARCHAR);
CREATE TEXT SEARCH DICTIONARY passages_en AS    split_text(case := 'lower') | normalize_tokens('en_US.UTF-8', accent := false)    WITH (frequency, position, offset);
CREATE INDEX passages_idx ON passages USING inverted (id, body passages_en);
INSERT INTO passages VALUES    (1, 'the quick brown fox jumps over the lazy dog'),    (2, 'a quick red fox runs fast'),    (3, 'First sentence here. The quick brown fox is in this sentence. Third one.');
VACUUM (REFRESH_TABLE) passages;

Match Operator

Function	Description
column @@ tsquery	Match predicate: rows where the indexed column satisfies the query.

column @@ tsquery

Filters to rows where the indexed column satisfies a TSQUERY.

Parameter	Type	Default	Meaning
column	any indexed column	—	A column covered by an inverted index.
tsquery	TSQUERY	—	The query to test against the column. A bare string literal is accepted and tokenized by the column's dictionary, unless its whole text is a query expression.

How it works. @@ is the single entry point that turns a TSQUERY into an inverted-index scan. The whole expression on the right of @@ is claimed by the index at bind time and evaluated by the index scan, not row by row. The operator is commutative — tsquery @@ column is identical. Although it is typed BOOLEAN, it is only valid in a WHERE clause against an inverted-indexed column; using it as a standalone expression (for example in the SELECT list) raises an error, so it is not a general-purpose boolean. Likewise the TSQUERY constructors below only have meaning inside an @@ match — evaluating one on its own raises an error.

Query	Matches id	Why
body @@ ts_phrase('fox')	1, 2	Both bodies contain fox.
body @@ 'fox'	1, 2	A bare string is a valid TSQUERY.
body @@ ts_phrase('unicorn')	(none)	No body contains unicorn.

SQL

SELECT id FROM docs_idx WHERE body @@ ts_phrase('fox') ORDER BY id;

 id
----
  1
  2

TSQUERY Constructors

Each returns a TSQUERY. A bare string literal is also a valid TSQUERY — it is tokenized by the column's dictionary (multi-token input uses OR semantics), unless its whole text is a query expression.

Function	Description
ts_phrase(text[, gap, text, ...])	Match the tokens of text as a phrase, with optional gaps.
ts_tokenize(text[, dictionary])	Tokenize text into a query, optionally with a named dictionary.
ts_like(pattern)	Match tokens against a SQL LIKE pattern.
ts_starts_with(prefix)	Match tokens beginning with prefix.
ts_regexp(pattern[, syntax])	Match tokens against a regular expression.
ts_levenshtein(text[, distance[, transpositions[, prefix]]])	Match tokens within an edit distance of text.
ts_ngram(text[, threshold])	Match by n-gram similarity.
ts_between(min, max, min_incl, max_incl)	Range match between two bounds.
ts_lt(value)	Match values less than value.
ts_le(value)	Match values less than or equal to value.
ts_gt(value)	Match values greater than value.
ts_ge(value)	Match values greater than or equal to value.
ts_any(list[, min_match])	OR over a list; at least min_match must match.
ts_all(list)	AND over a list.
ts_compound(must, must_not, should[, min_should_match])	Boolean query (the bool analog).

ts_phrase(text[, gap, text, ...][, slop := N])

Match a run of tokens in their indexed order, optionally separated by token gaps.

Parameter	Type	Default	Meaning
text	VARCHAR or BLOB	—	A phrase segment. It is tokenized by the column's dictionary; the positions of its tokens must be strictly adjacent, and tokens the dictionary puts at one position are alternatives for it.
gap	INTEGER or INTEGER[]	0 between successive segments	Number of tokens allowed between the two surrounding segments. An integer N means exactly N tokens between; a two-element array [min, max] allows a range. 0 means adjacent.
text, ...	VARCHAR/BLOB	—	Further segments, each preceded by its own gap.
slop	INTEGER (named)	0	Budget of position moves allowed when lining the query up with the document. Must be between 0 and 65535; incompatible with [min, max] interval gaps.

How it works. ts_phrase needs position on the column's dictionary, or the column's text in the index, as in a search table or a column also listed in INCLUDE; both return the same rows, and Phrases without positions describes what the second costs. A generate_shingles dictionary without position answers a phrase that one of its shingles covers without the column's text. The tokens of each text segment must appear adjacent and in order. A dictionary can put several tokens at one position — the synonyms of expand_solr_synonyms, or the n-grams of generate_ngrams that start at the same character — and any one of them matches that position: with the synonyms car, automobile, ts_phrase('red car') also matches red automobile. The optional gap arguments control how far apart consecutive segments may sit. The gap counts the tokens between the two segments — 0 is immediate adjacency, 2 means exactly two intervening tokens. A [min, max] array accepts any gap in that inclusive range. Without slop, order is always preserved: ts_phrase('a', 0, 'b') does not match b a.

Query	Matches id	Why
ts_phrase('quick brown fox')	1	Three adjacent tokens in order; only id 1 has brown.
ts_phrase('quick', 1, 'fox')	1, 2	Exactly one token (brown/red) sits between quick and fox.
ts_phrase('quick', 3, 'over')	1, 2	brown/red fox jumps are the three tokens between quick and over.
ts_phrase('fox', [0, 2], 'dog')	(none)	jumps over lazy are three tokens apart — outside the 0..2 range.

SQL

SELECT id FROM docs_idx WHERE body @@ ts_phrase('quick brown fox') ORDER BY id;

 id
----
  1

The gap form expresses proximity directly. To require quick within three tokens before over:

SQL

SELECT idFROM docs_idxWHERE body @@ ts_phrase('quick', [0, 3], 'over')ORDER BY id;

 id
----
  1
  2

Slop. slop := N spends a budget of N position moves to line the query up with the document, shared across the whole phrase: ts_phrase('quick fox', slop := 1) matches quick brown fox, because shifting fox one position costs one unit. An intervening token costs 1 per token and a transposed adjacent pair costs 2, so slop := 2 also matches fox quick. slop := 0 is an exact phrase.

Precisely, a match costs the spread of its shifts: shift each query token by its document position minus its query position, then take the largest shift minus the smallest. A swapped pair inside a longer phrase therefore still costs 2, so ts_phrase('quick brown fox', slop := 2) matches quick fox brown.

When a gap is declared, the budget counts deviation from that gap rather than from adjacency: ts_phrase('quick', 1, 'fox', slop := 1) accepts quick fox and quick a b fox — one step either side of the declared single-token gap. Interval gaps already express a range, so ts_phrase('a', [1, 3], 'b', slop := 2) is an error.

(...)::slop(N) applies the same budget, with the same 0 to 65535 range, as a modifier to an already-built phrase, including one from phraseto_tsquery. Lucene's "..."~N reaches it through to_tsquery. The forms are mutually exclusive: specifying slop twice on one phrase is an error, and ::slop on a non-phrase query (or on a ## part) is rejected.

See Phrase and Proximity Search for worked examples.

ts_tokenize(text[, dictionary])

Analyze text into a query using a chosen dictionary, overriding the column's default analysis.

Parameter	Type	Default	Meaning
text	VARCHAR/BLOB (or a LIST of them)	—	The text to tokenize. A list yields a LIST(TSQUERY), one per element, for use inside ts_any / ts_all.
dictionary	VARCHAR	the @@ column's dictionary	Name of the text-search dictionary to analyze with. The special value 'keyword' bypasses analysis and treats text as a single raw token.

How it works. The one-argument form analyzes text with the same dictionary as the column it is matched against, so the query and the index agree on casing, stemming and stop-words. Naming a dictionary forces a specific analyzer — useful when you want, say, exact 'keyword' matching against a column that is otherwise stemmed. Multi-token output is combined with OR. Tokens the dictionary puts at one position, such as the synonyms of a word, are one alternative that scores as its best-matching token.

Other value modifiers. ::score(scorer) and ::merge(policy) change how a subtree scores rather than what it matches; see Per-node score control.

::tokenize cast. The cast 'text'::tokenize('dictionary') is exactly equivalent to ts_tokenize('text', 'dictionary') and reads naturally inline — for example WHERE body @@ 'Running'::tokenize('exact'). The cast requires a dictionary name (use 'keyword' to bypass analysis); there is no no-argument cast form.

Query	Matches id	Why
body @@ ts_tokenize('quick', 'keyword')	1, 2	Raw token quick matched verbatim.
body @@ ts_tokenize('quick grey')	1, 2, 3	Two tokens combined with OR.
body @@ ts_tokenize('QUICK', 'keyword')	(none)	'keyword' skips lower-casing, so QUICK ≠ indexed quick.

SQL

SELECT id FROM docs_idx WHERE body @@ ts_tokenize('quick', 'keyword') ORDER BY id;

 id
----
  1
  2

ts_like(pattern)

Match indexed tokens against a SQL LIKE pattern.

Parameter	Type	Default	Meaning
pattern	VARCHAR/BLOB	—	A LIKE pattern. % matches any run of characters (including none), _ matches exactly one character. The pattern is matched against terms as they are stored in the index.

How it works. ts_like is applied to the indexed term form, not the raw text, so it is not tokenized further — match it against a column whose dictionary stores whole values (such as a keyword-style category column). It is the inverted-index analogue of SQL LIKE: a row matches when any of its indexed tokens satisfies the pattern.

Query	Matches id	Why
category @@ ts_like('sci%')	1, 2	sci-fi starts with sci.
category @@ ts_like('dram_')	3	drama is dram plus exactly one character.
category @@ ts_like('%edy')	4	comedy ends with edy.
category @@ ts_like('thriller')	(none)	No category equals thriller.

SQL

SELECT id, category FROM docs_idx WHERE category @@ ts_like('sci%') ORDER BY id;

 id | category
----+----------
  1 | sci-fi
  2 | sci-fi

ts_starts_with(prefix)

Match any indexed token beginning with prefix.

Parameter	Type	Default	Meaning
prefix	VARCHAR/BLOB	—	The literal leading substring a term must start with. Matched against the indexed term form, with no further tokenization.

How it works. A row matches when any of its indexed tokens begins with prefix. This is the classic autocomplete primitive — ts_starts_with('app') matches apple, application, app. It is equivalent to ts_like(prefix || '%') but expresses intent more clearly and is the building block behind the a* syntax of to_tsquery.

Query	Matches id	Why
body @@ ts_starts_with('turt')	3	turtle begins with turt.
body @@ ts_starts_with('qu')	1, 2	quick begins with qu.
body @@ ts_starts_with('zzz')	(none)	No token starts with zzz.

SQL

SELECT id FROM docs_idx WHERE body @@ ts_starts_with('turt') ORDER BY id;

 id
----
  3

ts_regexp(pattern[, syntax])

Match indexed tokens against a regular expression.

Parameter	Type	Default	Meaning
pattern	VARCHAR/BLOB	—	The regular expression. It must match a whole indexed term (anchored implicitly, not a substring search).
syntax	VARCHAR	'perl'	Dialect: 'perl' (RE2 / Perl-compatible) or 'posix' (POSIX ERE). Case-insensitive.

How it works. The pattern is applied to each indexed term and a row matches when any term matches. Because terms are stored in their analyzed form (lower-cased by the dictionary in our setup), write the pattern against that form — ts_regexp('QUICK') finds nothing, but the inline flag ts_regexp('(?i)QUICK') does (in 'perl' mode). The 'posix' dialect is handy for bracket-class patterns such as gr[ae]y.

A pattern that is not valid in its dialect, such as ts_regexp('(abc') or ts_regexp('\d+', 'posix'), fails the query with invalid regular expression: …, and so does one too large to compile.

A pattern's size never changes its answer: a long alternation — hundreds of generated names, synonyms or term lists — returns exactly the terms it matches, only more slowly past the point where the index can no longer prune the dictionary with it. Alternatives that begin or end the same way are merged before matching, so thousands of ^(the\s+)?<name>\b.* branches cost about as much as the distinct names they spell. A pattern that can only match a short list of words (up to 1024), such as x|y|zz or gr[ae]y, looks those words up directly, as ts_any does. A prepared statement compiles its patterns once; to reuse compiled patterns across queries as well, set sdb_pattern_cache_size to the number of bytes the server may keep for them.

Anchors and word boundaries keep their RE2 meaning inside a term: ^ and $ hold only at its ends (and, with (?m), around line breaks), and \b and \B look at the characters on either side. Over whole values indexed with a keyword() dictionary, ts_regexp('(?i)^(the\s+)?siemens\b.*') matches The Siemens AG and siemens financial services but not siemensland.

On a column indexed with generate_wildcard_ngrams, the pattern still matches whole terms, but it goes through the same filter as ts_like: the candidates come from the grams of the text the pattern requires, and each one is re-checked against the stored term unless the grams alone decide the match.

Query	Matches id	Why
body @@ ts_regexp('qu.*ck')	1, 2	quick matches the Perl pattern.
body @@ ts_regexp('gr[ae]y', 'posix')	3	grey matches the POSIX class.
body @@ ts_regexp('(?i)QUICK')	1, 2	The inline (?i) flag makes the match case-insensitive.
body @@ ts_regexp('z.*')	(none)	No term begins with z.

SQL

SELECT id FROM docs_idx WHERE body @@ ts_regexp('qu.*ck') ORDER BY id;

 id
----
  1
  2

Use the 'posix' dialect for a POSIX ERE bracket expression:

SQL

SELECT idFROM docs_idxWHERE body @@ ts_regexp('gr[ae]y', 'posix')ORDER BY id;

 id
----
  3

In a ## phrase, ts_regexp matches the token at its position:

SQL

SELECT idFROM docs_idxWHERE body @@ ('quick' ## ts_regexp('(brown|red)') ## 'fox')ORDER BY id;

 id
----
  1
  2

ts_levenshtein(text[, distance[, transpositions[, prefix]]])

Fuzzy match: find tokens within a bounded edit distance of text — the standard tolerance for typos.

Parameter	Type	Default	Meaning
text	VARCHAR/BLOB	—	The term to match approximately. When prefix is given, this is only the suffix that is fuzzy-matched (see below).
distance	INTEGER	auto by length: 0 for ≤ 2 chars, 1 for 3–5, 2 for ≥ 6	Maximum edit distance (insertions, deletions, substitutions). Allowed range 0–4 (capped at 3 when transpositions is true).
transpositions	BOOLEAN	true	When true, swapping two adjacent characters counts as one edit (Damerau–Levenshtein); when false, it counts as two.
prefix	VARCHAR	'' (empty)	A literal leading substring that must match exactly. The term actually matched is prefix || text; only the text portion spends the edit budget.

How it works. A term matches when its edit distance from the query is at most distance. The knobs around that rule:

• Auto distance. The one-argument form picks the distance from the query length: 0 for two characters or fewer, 1 for three to five, 2 from six up. Short queries tolerate fewer edits, which keeps them from drifting into unrelated tokens.
• Transpositions. On by default, so a single adjacent-character swap costs one edit instead of two: quikc reaches quick at distance 1, where strict Levenshtein needs distance 2.
• Prefix. Anchors an exact leading substring and fuzzy-matches only the rest, which both narrows the candidate set and speeds the scan. ts_levenshtein('X', 1, true, 'quic') requires the literal quic, then allows one edit on X, reaching quick.
• Expansion cap. sdb_levenshtein_max_terms (default 50) bounds how many dictionary terms the predicate expands to. The terms closest to the query survive; the rest neither match nor score. Set it to 0 to match every term within the edit distance, or narrow the candidate set with prefix. The cap applies per index segment, so a wide predicate can match more terms while they sit in separate segments than after a merge.
• Term enumeration. A predicate on the column that a ts_dict_* query enumerates is exempt from the cap, because there the terms are the result rather than a means to one. Other predicates in the same query keep it, and since enumeration only sees matching documents, capping one of those narrows the returned terms too.

Query	Matches id	Why
body @@ ts_levenshtein('quikc', 2)	1, 2	quikc → quick is within 2 edits.
body @@ ts_levenshtein('quikc', 1, true)	1, 2	The kc↔ck transposition is a single edit.
body @@ ts_levenshtein('quikc', 1, false)	(none)	Without transpositions, quikc → quick is 2 edits.
body @@ ts_levenshtein('X', 1, true, 'quic')	1, 2	Exact prefix quic plus one edit (X → k) reaches quick.

SQL

SELECT id FROM docs_idx WHERE body @@ ts_levenshtein('quikc', 2) ORDER BY id;

 id
----
  1
  2

Combine a fixed prefix with a small fuzzy suffix to keep typo-tolerance fast and focused — only terms beginning with quic are considered:

SQL

SELECT idFROM docs_idxWHERE body @@ ts_levenshtein('X', 1, true, 'quic')ORDER BY id;

 id
----
  1
  2

ts_ngram(text[, threshold])

Match by n-gram similarity — fuzzy matching that scores on shared character sequences rather than edit distance.

Parameter	Type	Default	Meaning
text	VARCHAR/BLOB	—	The term to match approximately.
threshold	DOUBLE	0.7	Minimum similarity, in 0.0–1.0. A term matches when the fraction of n-grams it shares with text is at least threshold. Lower values widen the match (higher recall); higher values tighten it (higher precision).

How it works. This requires a column tokenized with an n-gram dictionary of a single gram size (our bigram dictionary splits hello into he, el, ll, lo). The query string is split the same way, and a term's similarity is the share of the query's n-grams it contains in the same order, gaps allowed: help holds he and el of hello's four bigrams, a similarity of 0.5. N-gram similarity therefore tolerates insertions and deletions but not reordered chunks, and suits short strings and approximate matching where edit distance is too rigid. 1.0 demands every n-gram in order; 0.3 is permissive.

The matcher counts one n-gram per position, so a dictionary that puts several tokens at one position is rejected with ts_ngram needs a dictionary that emits one token per position. That covers different MIN_GRAM and MAX_GRAM (the defaults of generate_ngrams() are 2 and 3), PRESERVE_ORIGINAL and synonym stages.

Query	Matches id, title	Why
title @@ ts_ngram('hello')	1 hello	At the default 0.7, only hello shares enough bigrams.
title @@ ts_ngram('hello', 0.3)	1 hello, 2 help, 4 held	The lower threshold also admits help and held.
title @@ ts_ngram('hello', 0.99)	1 hello	Near-exact n-gram overlap required.
title @@ ts_ngram('zzzz', 0.3)	(none)	No title shares bigrams with zzzz.

SQL

SELECT id, title FROM titles_idx WHERE title @@ ts_ngram('hello', 0.3) ORDER BY id;

 id | title
----+-------
  1 | hello
  2 | help
  4 | held

Note

N-gram similarity is recall-oriented, and threshold is its only bound on candidate terms: there is no expansion cap here, unlike ts_levenshtein. Very low thresholds on large vocabularies can be broad; raise threshold to tighten results.

ts_between(min, max, min_incl, max_incl)

Range match between two bounds, with explicit control over each end's inclusivity.

Parameter	Type	Default	Meaning
min	any (matches the column)	— (required)	Lower bound, or NULL for unbounded below.
max	any (matches the column)	— (required)	Upper bound, or NULL for unbounded above.
min_incl	BOOLEAN	— (required)	true includes min (>=), false excludes it (>).
max_incl	BOOLEAN	— (required)	true includes max (<=), false excludes it (<).

How it works. ts_between performs a range scan over a numeric, temporal, boolean, text or BLOB column. Unlike BETWEEN in plain SQL, both inclusivity flags are explicit and required -- there is no default. Either bound may be NULL to make that side unbounded; both NULL matches every row, including rows where the column is NULL. (For a one-sided range you can also use the shorthand ts_lt/ts_le/ts_gt/ts_ge, which take a single non-NULL bound.)

On text and BLOB columns, each non-NULL bound is analyzed with the column's dictionary, just like the one-sided functions. A bound must produce one token: multiple tokens are an error, and no tokens makes the range match nothing. An empty-string token is still a valid bound. This supports keyword, normalize_tokens and collate_tokens dictionaries, as well as tokenizing dictionaries when each bound produces one token. Comparisons use the resulting token bytes, so collate_tokens ranges follow the dictionary's locale order.

A row matches when an indexed token satisfies both bounds. For dictionaries that emit one token per value, this agrees with combining the corresponding one-sided functions using &&. With multiple indexed tokens, the two separate comparisons can match different tokens in the same row. Inside a ## phrase, ts_between bounds remain verbatim token bytes at that phrase position.

Query	Matches id	Why
id @@ ts_between(2, 3, true, true)	2, 3	Closed interval [2, 3].
id @@ ts_between(2, 3, false, false)	(none)	Open interval (2, 3) contains no integer.
id @@ ts_between(NULL, 2, true, true)	1, 2	Unbounded below, up to and including 2.
id @@ ts_between(3, NULL, true, true)	3, 4	From 3 (inclusive) upward, unbounded above.

SQL

SELECT id FROM docs_idx WHERE id @@ ts_between(2, 3, true, true) ORDER BY id;

 id
----
  2
  3

A NULL bound makes one side unbounded — here, everything up to and including id = 2:

SQL

SELECT idFROM docs_idxWHERE id @@ ts_between(NULL, 2, true, true)ORDER BY id;

 id
----
  1
  2

ts_lt(value)

Match values strictly less than value — the one-sided shorthand for ts_between(NULL, value, ?, false).

Parameter	Type	Default	Meaning
value	any (matches the column)	—	Exclusive upper bound. Must be non-NULL (use ts_between for an unbounded side).

How it works. Matches every indexed value v with v < value. Inclusivity is fixed by the function name: ts_lt is strict (<).

Query	Matches id	Why
id @@ ts_lt(3)	1, 2	Values strictly below 3.
id @@ ts_lt(1)	(none)	Nothing is below the minimum id.

SQL

SELECT id FROM docs_idx WHERE id @@ ts_lt(3) ORDER BY id;

 id
----
  1
  2

ts_le(value)

Match values less than or equal to value.

Parameter	Type	Default	Meaning
value	any (matches the column)	—	Inclusive upper bound. Must be non-NULL.

How it works. Matches every indexed value v with v <= value; value itself is included.

Query	Matches id	Why
id @@ ts_le(2)	1, 2	Values up to and including 2.
id @@ ts_le(0)	(none)	Nothing is at or below 0.

SQL

SELECT id FROM docs_idx WHERE id @@ ts_le(2) ORDER BY id;

 id
----
  1
  2

ts_gt(value)

Match values strictly greater than value.

Parameter	Type	Default	Meaning
value	any (matches the column)	—	Exclusive lower bound. Must be non-NULL.

How it works. Matches every indexed value v with v > value.

Query	Matches id	Why
id @@ ts_gt(3)	4	Values strictly above 3.
id @@ ts_gt(4)	(none)	Nothing is above the maximum id.

SQL

SELECT id FROM docs_idx WHERE id @@ ts_gt(3) ORDER BY id;

 id
----
  4

ts_ge(value)

Match values greater than or equal to value.

Parameter	Type	Default	Meaning
value	any (matches the column)	—	Inclusive lower bound. Must be non-NULL.

How it works. Matches every indexed value v with v >= value; value itself is included.

Query	Matches id	Why
id @@ ts_ge(3)	3, 4	Values from 3 upward.
id @@ ts_ge(5)	(none)	Nothing is at or above 5.

SQL

SELECT id FROM docs_idx WHERE id @@ ts_ge(3) ORDER BY id;

 id
----
  3
  4

ts_any(list[, min_match])

OR over a list of sub-queries, with an optional "match at least N" threshold.

Parameter	Type	Default	Meaning
list	LIST(TSQUERY) (bare strings allowed)	—	The alternatives. Each element is a TSQUERY; a plain string is tokenized by the column dictionary.
min_match	INTEGER	1	How many alternatives a row must satisfy. Must be between 1 and the list length. 1 is a plain OR; raising it demands more of the alternatives.

How it works. ts_any is a disjunction with a tunable floor. At min_match = 1 it is a straight OR — match any alternative. Raising min_match turns it into an "N of M" query: with three alternatives and min_match = 2, a row must contain at least two of them. This is the equivalent of Elasticsearch's minimum_should_match (integer form) and the terms_set query. SereneDB takes an integer count only — it does not accept percentage or negative minimum_should_match formats, nor a per-document min-match field. Over ts_tokenize a position counts once: the synonyms of one word are one alternative, so min_match counts words, not synonyms. For "N of M" over predicates on different columns, use ::min_match(K) on an OR.

Query	Matches id	Why
body @@ ts_any(['quick', 'grey'])	1, 2, 3	Any one of the terms is enough.
body @@ ts_any(['quick', 'grey', 'red'], 2)	2	Only id 2 (quick + red) contains two of the three.
body @@ ts_any(['unicorn', 'dragon'])	(none)	Neither term appears.

SQL

SELECT id FROM docs_idx WHERE body @@ ts_any(['quick', 'grey'], 1) ORDER BY id;

 id
----
  1
  2
  3

Set min_match above 1 for an "N of M" query — here, rows containing at least two of the three terms:

SQL

SELECT idFROM docs_idxWHERE body @@ ts_any(['quick', 'grey', 'red'], 2)ORDER BY id;

 id
----
  2

ts_all(list)

AND over a list of sub-queries — every element must match.

Parameter	Type	Default	Meaning
list	LIST(TSQUERY) (bare strings allowed)	—	The conjuncts. A row matches only when it satisfies all of them.

How it works. ts_all is the conjunction (AND) of every element — equivalent to chaining the elements with &&, or to ts_any(list, len(list)). Use it to require that several tokens or sub-queries all appear in the same row. Over ts_tokenize the synonyms of one word are alternatives: any one of them satisfies that word.

Query	Matches id	Why
body @@ ts_all([ts_phrase('quick'), ts_phrase('brown')])	1	Only id 1 has both quick and brown.
body @@ ts_all([ts_phrase('quick'), ts_phrase('red')])	2	Only id 2 has both quick and red.
body @@ ts_all([ts_phrase('quick'), ts_phrase('grey')])	(none)	No row has both quick and grey.

SQL

SELECT id FROM docs_idx WHERE body @@ ts_all([ts_phrase('quick'), ts_phrase('brown')]) ORDER BY id;

 id
----
  1

ts_compound(must, must_not, should[, min_should_match])

Boolean query combining required, forbidden and optional clauses in one call — the SereneDB analog of the Elasticsearch bool query.

Parameter	Type	Default	Meaning
must	TSQUERY, LIST(TSQUERY) or NULL	—	Clauses that must all match (AND). NULL is an empty bucket.
must_not	TSQUERY, LIST(TSQUERY) or NULL	—	Clauses that must not match (exclusion). NULL is an empty bucket.
should	TSQUERY, LIST(TSQUERY) or NULL	—	Optional clauses; at least min_should_match of them must match. NULL is an empty bucket.
min_should_match	INTEGER	1	How many should clauses are required. Must be between 1 and the number of should clauses; supplying it with no should clauses is an error.

How it works. ts_compound mirrors Elasticsearch's bool query: must ≈ AND, must_not ≈ negation, should ≈ a tunable OR. A row matches when every must clause matches, no must_not clause matches, and at least min_should_match of the should clauses match. Each bucket accepts a single TSQUERY, a list of them, or NULL for "no clauses here". This packs the work of &&, !! and ts_any into one expression.

Query	Matches id	Why
ts_compound(ts_phrase('quick'), ts_phrase('grey'), [ts_phrase('lazy')])	1, 2	Must have quick, must not have grey, should have lazy.
ts_compound(ts_phrase('fox'), NULL, [ts_phrase('brown'), ts_phrase('jumps'), ts_phrase('lazy')], 2)	1, 2	Both fox rows have at least two of brown/jumps/lazy.
ts_compound(ts_phrase('fox'), NULL, [ts_phrase('brown'), ts_phrase('jumps'), ts_phrase('lazy')], 3)	1	Only id 1 has all three optional terms.

SQL

SELECT id FROM docs_idx WHERE body @@ ts_compound(ts_phrase('quick'), ts_phrase('grey'), [ts_phrase('lazy')]) ORDER BY id;

 id
----
  1
  2

Tighten the should bucket with min_should_match — require a fox row that also has all three optional terms brown, jumps and lazy:

SQL

SELECT idFROM docs_idxWHERE body @@ ts_compound(    ts_phrase('fox'),    NULL,    [ts_phrase('brown'), ts_phrase('jumps'), ts_phrase('lazy')],    3)ORDER BY id;

 id
----
  1

TSQUERY Operators

Operator	Description
a || b	OR — match either sub-query.
a && b	AND — match both sub-queries.
!! a	NOT — exclude matches of a (within a conjunction).
a ## b	Phrase adjacency, with optional gap or gap range.
a ^ factor	Boost a's contribution to the relevance score.

a || b

Disjunction (OR) of two sub-queries.

Operand	Type	Meaning
a, b	TSQUERY	Sub-queries. A row matches when either matches.

How it works. || combines two TSQUERY values into one that matches if at least one side does. It is the pairwise form of ts_any; chain it (a || b || c) for more alternatives.

Query	Matches id	Why
body @@ (ts_phrase('brown') || ts_phrase('grey'))	1, 3	brown is in id 1, grey in id 3.
body @@ (ts_phrase('unicorn') || ts_phrase('grey'))	3	Only the grey side matches.

SQL

SELECT id FROM docs_idx WHERE body @@ (ts_phrase('brown') || ts_phrase('grey')) ORDER BY id;

 id
----
  1
  3

a && b

Conjunction (AND) of two sub-queries.

Operand	Type	Meaning
a, b	TSQUERY	Sub-queries. A row matches only when both match.

How it works. && matches only rows satisfying both operands — the pairwise form of ts_all. It pairs naturally with !! to express "has X but not Y".

Query	Matches id	Why
body @@ (ts_phrase('quick') && ts_phrase('brown'))	1	Only id 1 has both.
body @@ (ts_phrase('quick') && ts_phrase('grey'))	(none)	No row has both.

SQL

SELECT id FROM docs_idx WHERE body @@ (ts_phrase('quick') && ts_phrase('brown')) ORDER BY id;

 id
----
  1

!! a

Unary negation (NOT) — excludes the matches of a.

Operand	Type	Meaning
a	TSQUERY	The sub-query to negate.

How it works. !!a is a hard exclusion (Elasticsearch must_not): it removes rows that match a. It is meaningful only inside a conjunction — something && !!a means "matches something but not a". A standalone negation has no positive clause to filter, so always combine it. SereneDB has no soft down-weighting query (Elasticsearch's boosting with a negative_boost); use ^ to raise a positive clause instead.

Query	Matches id	Why
body @@ (ts_phrase('quick') && !!ts_phrase('brown'))	2	Has quick, excludes the brown row.
body @@ (ts_phrase('fox') && !!ts_phrase('red'))	1	Has fox, excludes the red row.

SQL

SELECT id FROM docs_idx WHERE body @@ (ts_phrase('quick') && !!ts_phrase('brown')) ORDER BY id;

 id
----
  2

a ## b

Ordered proximity: require the sub-queries to appear close together, in order.

Operand	Type	Meaning
a, b	TSQUERY or VARCHAR	The two ends of the phrase.
gap (between)	INTEGER or INTEGER[]	Optional. a ## b is strict adjacency; a ## N ## b requires exactly N tokens between; a ## [min, max] ## b allows a gap range.

How it works. ## is an ordered proximity operator: a must precede b. The integer counts the tokens between the two ends — a ## b (no integer) and a ## 0 ## b both mean immediate adjacency, a ## 2 ## b means exactly two intervening tokens. Order matters: 'quick' ## 'brown' matches quick brown but 'brown' ## 'quick' does not.

Note

The integer in ## counts the tokens between the operands (0 = adjacent). The tsquery_phrase function and PostgreSQL's <-> use the opposite convention, where distance = 1 means adjacent. See tsquery_phrase.

Query	Matches id	Why
body @@ ('quick' ## 0 ## 'brown')	1	quick brown are adjacent in order.
body @@ ('brown' ## 0 ## 'quick')	(none)	Reversed order — ## is ordered.
body @@ ('quick' ## 1 ## 'fox')	1, 2	Exactly one token (brown/red) between them.
body @@ ('fox' ## 3 ## 'dog')	1, 2	jumps over lazy are the three tokens between.

SQL

SELECT id FROM docs_idx WHERE body @@ ('quick' ## 1 ## 'fox') ORDER BY id;

 id
----
  1
  2

a ^ factor

Boost: scale a sub-query's contribution to the relevance score.

Operand	Type	Meaning
a	TSQUERY	The sub-query to boost.
factor	DOUBLE (≥ 0)	Multiplier applied to a's score contribution. > 1 raises it, < 1 lowers it, 0 zeroes it.

How it works. ^ reweights a clause for relevance scoring without changing which rows match — the result set is identical, only the order differs once you ORDER BY a score. The factor multiplies that clause's contribution to the score: with ^ 3.0, a row that matches the boosted clause earns three times its normal BM25 weight from it (the worked example below verifies the boosted rows score exactly 3× the unboosted). factor > 1 promotes a clause, 0 < factor < 1 demotes it and 0 neutralizes its score contribution (the clause still matches). Factors compose multiplicatively when nested — (a ^ 2) ^ 3 weights a by 6. Typical use is favouring one alternative in a disjunction, e.g. title_match ^ 3 || body_match. The factor must be non-negative, and the cast form a::boost(factor) is equivalent.

Query	Matches id	Effect
body @@ (ts_phrase('fox') || ts_phrase('quick') ^ 2.0)	1, 2	Same rows as the un-boosted OR; quick matches simply score higher.

SQL

SELECT id FROM docs_idx WHERE body @@ (ts_phrase('fox') || ts_phrase('quick') ^ 2.0) ORDER BY id;

 id
----
  1
  2

To see the effect, boost one alternative and order by BM25:

SQL

SELECT id, BM25(scored_idx.tableoid) AS scoreFROM scored_idxWHERE body @@ (ts_phrase('fox') || ts_phrase('quick') ^ 5.0)ORDER BY score DESC, id;

 id | score
----+------------
  1 |  2.5596745
  4 |   2.279048
  2 | 0.38217354
  3 | 0.19988364

Matching K of N Predicates

Modifier	Description
(p1 OR p2 OR ...)::min_match(K)	Match rows that satisfy at least K of the OR's branches.
query::min_match(K)	Match rows that satisfy at least K of a TSQUERY's alternatives: its words, its || branches or its ts_any elements.

(p1 OR p2 OR ...)::min_match(K)

Turns an OR of index predicates into an "at least K of N" filter. The branches can test different columns and mix any predicates the inverted index answers: @@ matches, comparisons, ranges, IN, IS NULL and their negations. Inside a negated OR over nullable columns, fewer of them work, as the list of failing cases below says.

Parameter	Type	Meaning
p1 OR p2 OR ...	BOOLEAN	The branches. Every branch has to be a predicate the inverted index answers.
K	integer literal	How many branches a row must satisfy. Must be between 1 and the number of branches.

How it works. ts_any(list, K) counts alternatives within one column. ::min_match(K) does the same across columns, on an OR written in SQL. The index evaluates the whole OR as a single node that requires K of its branches, so no row is fetched to count them. K = 1 is the plain OR, and K equal to the number of branches is the AND of all of them. This is the cross-field form of Elasticsearch's minimum_should_match on a bool query's should clauses.

Count the branches as written after the ORs are flattened: in ((a OR b) OR c)::min_match(2) there are three branches. A branch that is always false, such as a bound $1 AND ... with $1 = false, still counts and never matches, and two identical branches count twice. To count a OR b as one branch, give it a modifier of its own: ((a OR b)::min_match(1) OR c)::min_match(2).

Groups nest. A group is one branch of the OR around it, so ((a OR b OR c)::min_match(2) OR d)::min_match(2) matches rows that have d and at least two of a, b and c. A group can also stand in a plain OR or an AND, next to other groups or next to an AND branch. ::merge, ::boost and ::score combine with ::min_match on the same group, in either order.

Where the threshold has no meaning, the query fails instead of ignoring the modifier:

• on a single predicate or on an AND. That includes a predicate with alternatives of its own, such as (body @@ 'quick red fox')::min_match(2), ts_any or IN. To count the words of one @@ match, put the threshold on its query: body @@ 'quick red fox'::min_match(2);
• when K is below 1 or above the number of branches;
• twice on the same group, as in (a OR b OR c)::min_match(2)::min_match(1). Each group takes one threshold;
• directly under NOT. "Fewer than K of n" is "at least n - K + 1 of the negations", so write (NOT a OR NOT b OR ...)::min_match(n - K + 1) instead. A group inside a larger negated OR, as in NOT ((a OR b)::min_match(2) OR c), is fine: a branch on a NULL value is unknown, so the group is false only when fewer than K branches can still match;
• inside a negated OR over nullable columns, unless every branch, both in the group and in the rest of that OR, is a comparison, BETWEEN, IN, NOT IN, LIKE, starts_with or an @@ match (also through phrase_matches and the other convenience predicates). An IS NULL, IS NOT NULL, NOT, AND or nested group branch there fails with negated group over nullable columns mixes index-only search predicates with a shape that must stay a row filter, or, when the negated OR has no @@ match, with ::min_match(K) is only meaningful on an OR of predicates the inverted index answers. To keep an IS NULL or IS NOT NULL branch, write the negation out: NOT (g OR c) is NOT g AND NOT c, and NOT g is the n - K + 1 rewrite above;
• when a branch is not an index predicate;
• outside a WHERE clause on an inverted index, for example in the SELECT list.

In the setup, body, dual and category are nullable, so a group with a dual IS NULL branch fails inside a negated OR. Written out, the same filter runs on the index:

SQL

SELECT idFROM docs_idxWHERE NOT ((body @@ 'quick' OR dual IS NULL)::min_match(2) OR category = 'drama');
SELECT idFROM docs_idxWHERE (NOT body @@ 'quick' OR dual IS NOT NULL)::min_match(1) AND category <> 'drama'ORDER BY id;

error negated group over nullable columns mixes index-only search predicates with a shape that must stay a row filter

 id
----
  1
  4

The branches' queries can be bound parameters: (a @@ $1 OR b @@ $2)::min_match(2).

Query	Matches id	Why
(body @@ 'quick' OR category = 'drama' OR id >= 2)::min_match(2)	2, 3	id 2 has quick and id >= 2, and id 3 is a drama with id >= 2. id 1 and id 4 satisfy one branch each.
(body @@ 'quick' OR category = 'drama' OR id >= 2)::min_match(1)	1, 2, 3, 4	The plain OR.

SQL

SELECT idFROM docs_idxWHERE (body @@ 'quick' OR category = 'drama' OR id >= 2)::min_match(2)ORDER BY id;
SELECT idFROM docs_idxWHERE (body @@ 'quick' OR category = 'drama' OR id >= 2)::min_match(1)ORDER BY id;

 id
----
  2
  3

 id
----
  1
  2
  3
  4

query::min_match(K)

The same threshold on a TSQUERY value, inside one @@. Like the other TSQUERY modifiers it travels with the value, so it also works on a bound parameter: body @@ $1::min_match(2) with $1 = 'quick red fox' matches rows that have at least two of the three words. This is Elasticsearch's match query with minimum_should_match.

Parameter	Type	Meaning
query	TSQUERY	The alternatives to count.
K	integer literal	How many alternatives a row must satisfy, at least 1.

How it works. The threshold goes to the OR at the top of the query, and what counts as one alternative depends on its shape:

• Several words (a bare string, ts_tokenize, ::tokenize, ts_any(ts_tokenize([...]))): one alternative per word, with the synonyms of a word counting once. K above the number of words is capped at that number, so a one-word search with ::min_match(2) matches that word.
• A || chain: one alternative per operand of the whole chain, so (a || b || c)::min_match(2) has three. A parenthesized group with a modifier of its own is one operand. A bound parameter that holds a || chain, for example as query text, adds its operands to the count. K above the number of operands is an error.
• ts_any(list): one alternative per element, the same as ts_any(list, K). Given both, ::min_match wins: ts_any(list, 1)::min_match(2) requires two elements. K above the number of elements is an error.
• Anything else (a phrase, &&, !!, a range, to_tsquery) is one alternative: K = 1 changes nothing, a larger K is an error.

K above the count. Where you list the alternatives yourself (the operands of ||, the elements of ts_any(list), the branches of an OR of predicates), their number is in the query text, so a larger K is a mistake and the query fails. Where the dictionary derives them from text (the words of a string or of ts_tokenize), their number depends on stopwords, repeated words, synonyms and the column's dictionary, and is often unknown when the query is written, for example for text a user typed into a search box. There a larger K is capped: a row has to contain all of the words. Elasticsearch documents the same cap for minimum_should_match, but its implementation returns no hits when K exceeds the terms of a query with several terms.

A second ::min_match replaces the first, like a second ::merge or ::score: 'a b c'::min_match(2)::min_match(1) requires one word, and so does a bound parameter $1::min_match(1) whose value already carries ::min_match(2), such as the query text 'a b c'::min_match(2). NOT and !! negate the whole threshold: NOT body @@ 'quick red grey'::min_match(2) matches rows that have fewer than two of the words, and !! (('a'::TSQUERY || 'b')::min_match(2) || 'c') matches rows that have neither both a and b nor c. ::merge, ::boost and ::score combine with it.

Query	Matches id	Why
body @@ 'quick red grey'::min_match(2)	2	Only id 2 has two of the words, quick and red.
body @@ (ts_phrase('fox') || ts_phrase('turtle') || ts_phrase('lazy'))::min_match(2)	1, 2	Both have fox and lazy; id 3 has only turtle.

SQL

SELECT idFROM docs_idxWHERE body @@ 'quick red grey'::min_match(2)ORDER BY id;
SELECT idFROM docs_idxWHERE body @@ (ts_phrase('fox') || ts_phrase('turtle') || ts_phrase('lazy'))::min_match(2)ORDER BY id;

 id
----
  2

 id
----
  1
  2

PostgreSQL-Compatible Parsers

Each accepts a single string and returns a TSQUERY. These are SereneDB inverted-index queries, not PostgreSQL tsvector/tsquery.

Function	Description
to_tsquery(text)	Parse a Lucene-style query (AND/OR, +/-, *, ~, phrases, grouping, boost).
plainto_tsquery(text)	Tokenize and AND the terms.
phraseto_tsquery(text)	Treat text as a phrase.
websearch_to_tsquery(text)	Web-search syntax: quoted substrings are phrases, OR separates alternatives.
tsquery_phrase(a, b[, distance])	Function form of the ## phrase operator.

to_tsquery(text)

Parse a single Lucene-style query string into a TSQUERY.

Parameter	Type	Default	Meaning
text	VARCHAR	—	A Lucene / Elasticsearch query_string-style expression (see the grammar below). Parsing is strict: a malformed expression raises an error. For lenient, user-facing input use websearch_to_tsquery.

How it works. to_tsquery runs the full query parser — the richest of the parsers. Beyond the AND / OR / NOT boolean keywords it supports the common operators of the Lucene classic query-parser grammar:

Syntax	Meaning
a AND b · a OR b · a NOT b	Boolean conjunction / disjunction / exclusion.
+a	a is required.
-a	a is excluded.
a*	Prefix (wildcard) match.
a~N	Fuzzy match within edit distance N.
"a b"	Phrase match.
"a b"~N	Proximity phrase: a and b within N positions.
(a b)	Grouping.
a^N	Boost a's relevance contribution by factor N.

These combine freely. Despite the PostgreSQL-compatible name, this builds a SereneDB inverted-index query, not a PostgreSQL tsquery.

A column on the left of @@ names the field, so every term in the query searches it and a field:term prefix is rejected. To let the query choose its own fields, put the relation's tableoid on the left instead — the same handle BM25 takes. The operand then means "this index" rather than "this field", and every term must name one:

sql

SELECT id FROM docs_idx d
WHERE d.tableoid @@ to_tsquery('title:fox OR body:dog');

This is the only way to express one boolean spanning several fields: title @@ q OR body @@ q builds two independent queries, so an exclusion in q is scoped to whichever field matched it, while the form above excludes across the whole row. A bare term is an error here, since it has no field to search. Only to_tsquery takes this operand, on its own or combined with ||, &&, !! and ^. Every other constructor searches a single field, so it needs a column on the left.

A prefix on a group applies to every term inside it, so title:(fox OR dog) searches title for both. A term inside the group can still name its own field: in title:(fox body:dog cat) only dog searches body, and cat searches title again. A field never reaches past the group it was named in.

Query	Matches id	Why
body @@ to_tsquery('quick AND brown')	1	Both terms required.
body @@ to_tsquery('+fox -red')	1	Has fox, excludes red.
body @@ to_tsquery('qui*')	1, 2	Prefix qui matches quick.
body @@ to_tsquery('quikc~2')	1, 2	Fuzzy match within 2 edits of quick.
body @@ to_tsquery('"quick brown"~3')	1	quick and brown within three positions.
body @@ to_tsquery('xyzzy')	(none)	Term not present.

SQL

SELECT id FROM docs_idx WHERE body @@ to_tsquery('quick AND brown') ORDER BY id;

 id
----
  1

The + (required) and - (excluded) operators, grouping and the others combine freely — for example, match rows containing fox but not red:

SQL

SELECT id FROM docs_idx WHERE body @@ to_tsquery('+fox -red') ORDER BY id;

 id
----
  1

Wildcard, fuzzy and proximity operators compose too — a prefix on one term plus a fuzzy match on another:

SQL

SELECT idFROM docs_idxWHERE body @@ to_tsquery('qui* AND f0x~1')ORDER BY id;

 id
----
  1
  2

plainto_tsquery(text)

Tokenize text and combine the terms with AND.

Parameter	Type	Default	Meaning
text	VARCHAR	—	Free text. It is tokenized by the column dictionary and the resulting terms are joined with AND. Operators are not interpreted — +, -, quotes and * are treated as ordinary characters.

How it works. plainto_tsquery is the "all words must appear" parser: it splits text into terms and requires every term, with no order constraint. It is the conjunctive counterpart to a bare string literal (which uses OR). Tokens the dictionary puts at one position are alternatives: with the one-way rule car => automobile, auto, plainto_tsquery('red car') needs red and either automobile or auto, and the pair scores as its better match.

Query	Matches id	Why
body @@ plainto_tsquery('quick brown')	1	Both quick and brown required.
body @@ plainto_tsquery('quick fox')	1, 2	Both rows have quick and fox.
body @@ plainto_tsquery('quick grey')	(none)	No row has both.

SQL

SELECT id FROM docs_idx WHERE body @@ plainto_tsquery('quick brown') ORDER BY id;

 id
----
  1

phraseto_tsquery(text)

Treat text as an exact ordered phrase.

Parameter	Type	Default	Meaning
text	VARCHAR	—	Free text tokenized and matched as a contiguous, ordered phrase.

How it works. phraseto_tsquery tokenizes text and requires the tokens to appear adjacent and in order — equivalent to ts_phrase for a single string. Use it when word order matters.

Query	Matches id	Why
body @@ phraseto_tsquery('over lazy dog')	1, 2	The three words are adjacent and in order in both.
body @@ phraseto_tsquery('dog lazy over')	(none)	Same words, wrong order.

SQL

SELECT id FROM docs_idx WHERE body @@ phraseto_tsquery('over lazy dog') ORDER BY id;

 id
----
  1
  2

websearch_to_tsquery(text)

Parse forgiving, web-search-bar syntax into a TSQUERY.

Parameter	Type	Default	Meaning
text	VARCHAR	—	A search-engine-style string. Quoted substrings become phrases, the OR keyword separates alternatives, a leading - excludes a term and unquoted words are otherwise combined with AND.

How it works. This is the parser for untrusted, user-facing input: unlike to_tsquery it never raises on malformed syntax — stray operators are simply treated as text. It recognizes "quoted phrases", the literal OR keyword, and a leading - for exclusion; everything else is AND-ed. An unquoted word that the dictionary splits into several tokens, such as wi-fi, matches as a phrase, as in PostgreSQL; on a column with neither the position feature nor its text in the index every part is required instead. Synonyms of a word are alternatives, and a row matching several of them scores as its best match.

Query	Matches id	Why
body @@ websearch_to_tsquery('"quick brown" OR "grey turtle"')	1, 3	Either phrase.
body @@ websearch_to_tsquery('fox -red')	1	Has fox, excludes red.
body @@ websearch_to_tsquery('quick grey')	(none)	Unquoted words are AND-ed; no row has both.

SQL

SELECT id FROM docs_idx WHERE body @@ websearch_to_tsquery('"quick brown" OR "grey turtle"') ORDER BY id;

 id
----
  1
  3

tsquery_phrase(a, b[, distance])

Function form of a two-term proximity phrase, using PostgreSQL's distance convention.

Parameter	Type	Default	Meaning
a	TSQUERY	—	First (left) sub-query.
b	TSQUERY	—	Second (right) sub-query, required to follow a.
distance	INTEGER	1 (adjacent)	Number of lexemes between the start of a and the start of b, PostgreSQL-style: 1 means adjacent, 2 means one token in between. Must be ≥ 1.

How it works. tsquery_phrase is the function spelling of an ordered proximity phrase, matching PostgreSQL's <N> / tsquery_phrase semantics where distance = 1 is adjacency. This is the opposite counting convention from the ## operator, where the integer counts the tokens between the operands (0 = adjacent). Pick whichever reads more clearly — they target the same positions.

Query	Matches id	Why
body @@ tsquery_phrase('quick'::TSQUERY, 'brown'::TSQUERY)	1	Default distance = 1: quick brown adjacent.
body @@ tsquery_phrase('quick'::TSQUERY, 'fox'::TSQUERY, 2)	1, 2	distance = 2: one token (brown/red) between.
body @@ tsquery_phrase('quick'::TSQUERY, 'fox'::TSQUERY, 1)	(none)	distance = 1 demands adjacency, but a token sits between them.

SQL

SELECT id FROM docs_idx WHERE body @@ tsquery_phrase('quick'::TSQUERY, 'fox'::TSQUERY, 2) ORDER BY id;

 id
----
  1
  2

Convenience Predicates

Sugar that rewrites to @@ at bind time. Each takes the indexed column as its first argument and returns BOOLEAN.

Function	Description
phrase_matches(column, text[, text, ...])	Equivalent to column @@ ts_phrase(...).
ngram_matches(column, text[, threshold])	Equivalent to column @@ ts_ngram(...).
levenshtein_matches(column, text, distance[, transpositions[, prefix]])	Equivalent to column @@ ts_levenshtein(...).
has_all_tokens(column, list)	Equivalent to column @@ ts_all(list).
has_any_tokens(column, list[, min_match])	Equivalent to column @@ ts_any(list[, min_match]).

phrase_matches(column, text[, text, ...])

Phrase-match sugar for column @@ ts_phrase(text, ...).

Parameter	Type	Default	Meaning
column	any indexed column	—	The inverted-indexed column to match.
text	VARCHAR/BLOB	—	A phrase segment, as in ts_phrase.
text, ...	INTEGER/INTEGER[] + VARCHAR	—	Optional gap, text pairs, identical to ts_phrase's gap grammar.

How it works. phrase_matches(column, ...) rewrites at bind time to column @@ ts_phrase(...), inheriting the full gap grammar. It reads as a predicate, which is convenient when the @@ form feels verbose.

Query	Matches id	Why
phrase_matches(body, 'quick brown')	1	Adjacent phrase, only in id 1.
phrase_matches(body, 'quick', 3, 'over')	1, 2	Gap form: three tokens between quick and over.
phrase_matches(body, 'brown grey')	(none)	Those words never appear adjacent.

SQL

SELECT id FROM docs_idx WHERE phrase_matches(body, 'quick brown') ORDER BY id;

 id
----
  1

ngram_matches(column, text[, threshold])

N-gram-match sugar for column @@ ts_ngram(text[, threshold]).

Parameter	Type	Default	Meaning
column	n-gram-indexed column	—	The column to match.
text	VARCHAR/BLOB	—	Term to match by similarity.
threshold	DOUBLE	0.7	Minimum n-gram similarity, as in ts_ngram.

How it works. Rewrites to column @@ ts_ngram(text[, threshold]); see ts_ngram for the similarity model.

Query	Matches id, title	Why
ngram_matches(title, 'hello', 0.3)	1 hello, 2 help, 4 held	All share enough bigrams at 0.3.
ngram_matches(title, 'hello')	1 hello	At the default 0.7, only hello qualifies.

SQL

SELECT id, title FROM titles_idx WHERE ngram_matches(title, 'hello', 0.3) ORDER BY id;

 id | title
----+-------
  1 | hello
  2 | help
  4 | held

levenshtein_matches(column, text, distance[, transpositions[, prefix]])

Fuzzy-match sugar for column @@ ts_levenshtein(...).

Parameter	Type	Default	Meaning
column	any indexed column	—	The column to match.
text	VARCHAR/BLOB	—	Term to match approximately.
distance	INTEGER	— (required here)	Maximum edit distance. Unlike ts_levenshtein, the predicate has no auto-distance form, so distance is required.
transpositions	BOOLEAN	true	Whether an adjacent-character swap counts as one edit.
prefix	VARCHAR	''	Exact leading substring; only the suffix spends the edit budget.

How it works. Rewrites to column @@ ts_levenshtein(text, distance[, transpositions[, prefix]]). See ts_levenshtein for the edit-distance and prefix semantics.

Query	Matches id	Why
levenshtein_matches(body, 'quikc', 2)	1, 2	Within 2 edits of quick.
levenshtein_matches(body, 'quikc', 1, false)	(none)	Without transpositions, quikc → quick is 2 edits.

SQL

SELECT id FROM docs_idx WHERE levenshtein_matches(body, 'quikc', 2) ORDER BY id;

 id
----
  1
  2

has_all_tokens(column, list)

True when every token in list is present.

Parameter	Type	Default	Meaning
column	any indexed column	—	The column to match.
list	LIST(VARCHAR)	—	Tokens that must all appear. Each element is tokenized by the column dictionary.

How it works. Tokenizes each element and requires all of them — sugar for column @@ ts_all(ts_tokenize(list)). Use it for "contains every one of these words" filters.

Query	Matches id	Why
has_all_tokens(body, ['quick', 'brown'])	1	Only id 1 has both.
has_all_tokens(body, ['quick', 'fox'])	1, 2	Both have quick and fox.
has_all_tokens(body, ['quick', 'grey'])	(none)	No row has both.

SQL

SELECT id FROM docs_idx WHERE has_all_tokens(body, ['quick', 'brown']) ORDER BY id;

 id
----
  1

has_any_tokens(column, list[, min_match])

True when at least min_match tokens in list are present.

Parameter	Type	Default	Meaning
column	any indexed column	—	The column to match.
list	LIST(VARCHAR) (or a single VARCHAR)	—	Candidate tokens.
min_match	INTEGER	1	Minimum number of tokens that must appear. 1 is a plain OR.

How it works. Tokenizes the candidates and requires at least min_match of them — sugar for column @@ ts_any(ts_tokenize(list)[, min_match]). Raise min_match for an "N of M" filter, exactly as in ts_any.

Query	Matches id	Why
has_any_tokens(body, ['grey', 'red'])	2, 3	red in id 2, grey in id 3.
has_any_tokens(body, ['quick', 'grey', 'red'], 2)	2	Only id 2 has two of the three.
has_any_tokens(body, ['unicorn', 'dragon'])	(none)	Neither appears.

SQL

SELECT id FROM docs_idx WHERE has_any_tokens(body, ['grey', 'red'], 1) ORDER BY id;

 id
----
  2
  3

NULL Checks

There is no dedicated TSQUERY constructor for nullness — the plain SQL operators do the job and the index claims them.

column IS NULL / column IS NOT NULL

Match rows by the nullness of an indexed column.

How it works. Every indexed column carries a null-marker term in the index; IS NULL compiles to a lookup of that term and IS NOT NULL to its negation, so both run as posting-list reads — no INCLUDE storage or table probe needed. On columns the index does not cover, the same predicates simply run row-level; the syntax is identical either way. In the setup, dual has one NULL row.

Query	Matches id	Why
dual IS NULL	2	id 2 is the only row whose dual is NULL.
dual IS NOT NULL	1, 3, 4	Every other row has a value.

SQL

SELECT id FROM docs_idx WHERE dual IS NULL ORDER BY id;

 id
----
  2

SQL

SELECT id FROM docs_idx WHERE dual IS NOT NULL ORDER BY id;

 id
----
  1
  3
  4

Utility Functions

Function	Description
ts_lexize(dictionary, text)	Return the tokens a dictionary produces for text.
minhash(tokens, num_hashes)	Reduce a token list to a MinHash signature of at most num_hashes components.

ts_lexize(dictionary, text)

Return the tokens a dictionary produces for text — the tool for inspecting analysis.

Parameter	Type	Default	Meaning
dictionary	VARCHAR	—	Name of an existing text-search dictionary. It must exist in the catalog ('keyword' is not a real dictionary here).
text	VARCHAR or LIST(VARCHAR)	—	The text to analyze. A list analyzes each element and concatenates the results; an element the dictionary rejects is dropped like a NULL element.

How it works. ts_lexize is the only function on this page that runs on its own (not inside @@): it applies a named dictionary's analysis pipeline — lower-casing, stemming, stop-word removal, n-gram splitting — and returns the resulting lexemes as a LIST(VARCHAR). A dictionary has no call form of its own, so ts_lexize is how a query runs one; it takes the name as a value, which is what a query that picks a dictionary per row needs. Use it to see exactly how a query string or a document will be tokenized when tuning an index: if your search misses, lexize both the query and the source text and compare. As in PostgreSQL, the result tells a rejected value from an empty one: a value the dictionary rejects — such as a NULL result of a sql dictionary, or input a geo dictionary cannot parse — returns NULL, while a value it accepts without producing tokens, such as a stop word, returns an empty list.

Input	Tokens	Why
ts_lexize('en', 'Quick BROWN')	{quick, brown}	The en text dictionary splits at word boundaries and lower-cases each word.
ts_lexize('bigram', 'help')	{he, el, lp}	The bigram n-gram dictionary emits overlapping 2-grams.

SQL

SELECT ts_lexize('en', 'Quick BROWN') AS ts_lexize;

 ts_lexize
---------------
 {quick,brown}

Lexize against an n-gram dictionary to see how a term is split for ts_ngram matching:

SQL

SELECT ts_lexize('bigram', 'help') AS ts_lexize;

 ts_lexize
------------
 {he,el,lp}

minhash(tokens, num_hashes)

Reduce a list of tokens to a MinHash signature and return it as a LIST(BLOB). Two documents that share most of their tokens share most of their signature, so the number of matching components estimates the Jaccard similarity of their token sets.

Parameter	Type	Default	Meaning
tokens	LIST(VARCHAR) or LIST(BLOB)	—	The token set to summarize. A NULL list yields NULL; NULL elements are skipped.
num_hashes	INTEGER	—	Signature width. Must be a foldable constant and >= 1, or the query fails when it is bound.

Every element is hashed to a 64-bit value and the num_hashes smallest distinct hashes are kept, each returned as 8 bytes. The result depends only on which distinct tokens the input holds, not on their order or their frequency, and it is shorter than num_hashes when the input holds fewer distinct tokens. The components are opaque hashes, so match whole signatures — index the signature as an expression, or build it in the expression of a sql dictionary, and compare with ts_any where min_match is the similarity threshold. The minhash page works both shapes through.

Coming from Elasticsearch

The functions on this page cover most of the Elasticsearch / OpenSearch query DSL. This table maps each DSL query to its SereneDB equivalent (the left column links to the Elasticsearch reference).

Elasticsearch / OpenSearch query	SereneDB
match (analyzed terms, OR)	bare string, or ts_tokenize
match with operator: and	plainto_tsquery
match with minimum_should_match	query::min_match(K), for example body @@ 'quick red fox'::min_match(2)
match_phrase	ts_phrase, phraseto_tsquery
match_phrase with slop	ts_phrase with slop := N or ::slop(N); same semantics
term / terms	token literal, has_any_tokens
terms_set (match N of M)	ts_any with min_match
bool should over several fields with minimum_should_match	(p1 OR p2 OR ...)::min_match(K)
prefix	ts_starts_with
wildcard	ts_like or ts_regexp
regexp	ts_regexp
fuzzy	ts_levenshtein
range	ts_between, ts_lt/ts_le/ts_gt/ts_ge
exists	Plain SQL IS NOT NULL / IS NULL — the index claims both on indexed columns.
bool (must/must_not/should)	ts_compound, or &&/!!/ts_any
query_string / simple_query_string	to_tsquery (strict), websearch_to_tsquery (lenient)

Notable differences

Elasticsearch features without a direct SereneDB equivalent, and what to use instead:

Elasticsearch / OpenSearch	SereneDB
minimum_should_match percentage / negative / combination forms	integer count only (ts_any, ts_compound, ::min_match(K))
fuzziness: AUTO	one-argument ts_levenshtein auto-picks a distance by term length
max_expansions (fuzzy / prefix expansion cap)	fuzzy: sdb_levenshtein_max_terms (session-level, per segment, default 50); prefix: no cap
multi_match / combined_fields / field:term scoping	tableoid @@ to_tsquery with field:term prefixes, or one @@ per column combined with OR
constant_score	none; ORDER BY a literal, or raw_boost (see Ranking)
boosting (negative_boost)	none; raise a clause with ^ or exclude with !!
match_phrase_prefix / match_bool_prefix	combine ts_phrase with ts_starts_with
more_like_this	none; use minhash signatures or vector similarity (Vector Search)

See also

• Relevance Scoring · Highlighting · Vector Distance · Geospatial
• Inverted Index · Full-Text Search
• tsquery data type
• CREATE TEXT SEARCH DICTIONARY