SQLearnAcademy
←Function reference
Full-text searchPostgreSQL only

TO_TSQUERY

Builds a full-text search query: words with “and”, “or” and “not” conditions.

Syntax

TO_TSQUERY('<language config>', '<query>')
WEBSEARCH_TO_TSQUERY('<language config>', <search box text>)

In a query, words are joined with operators: & — “and”, | — “or”, ! — “not”, <-> — “one right after the other”. Words are reduced to stems the same way as in TO_TSVECTOR. WEBSEARCH_TO_TSQUERY accepts ordinary search box text — words, quotes and minus signs — and turns it into a query by itself.

A query with “and”
SELECT TO_TSQUERY('english', 'running & foxes') AS query;
query
'run' & 'fox'
“And” and “not” in a search
SELECT TO_TSVECTOR('english', 'Fast delivery and friendly support')
@@ TO_TSQUERY('english', 'delivery & !slow') AS match;
match
true

There's delivery and no slow — so it's a match.

Like a search engine
SELECT WEBSEARCH_TO_TSQUERY('english', 'fast delivery -slow') AS query;
query
'fast' & 'deliveri' & !'slow'

The minus before a word became “not”, and the space — “and”. deliveri is the stem of the word delivery.

Watch out

A space between words is an error: TO_TSQUERY('english', 'fast delivery') says syntax error in tsquery. Join words with & or use WEBSEARCH_TO_TSQUERY.

Don't pass user input to TO_TSQUERY: any stray character breaks the query. For a search box there's WEBSEARCH_TO_TSQUERY or PLAINTO_TSQUERY.

The query and document configurations must match: TO_TSVECTOR('english', 'running') @@ TO_TSQUERY('simple', 'running') gives false, because the document holds the stem run and the query holds the whole word running.

Learn more in the course

Full-text search