PostgreSQL: Full text search with the “websearch” syntax

2024-01-03 Searching the stars for an answer.

PostgreSQL’s powerful full text search feature supports several query syntaxes. Of these, a website search feature should typically pick the websearch syntax. websearch copies some features from popular search engines, as covered below, offering familiar short syntax to users. It is also forgiving and will never raise a syntax error for user input, whilst other syntaxes can.

The syntax

The websearch syntax discards stop words, such as “a” or “the” in English, then parses according to these rules:

Individual words match independently.
Double-quoted phrases match as a single unit.
The word or (case-insensitive) specifies an “or” condition between two words or phrases.
A - prefix specifies to not match the following word or phrase.

The below table gives some examples mapping queries to their tsquery representations.

Query	tsquery	Notes
`the donkey`	`'donkey'`	Stop word “the” removed
`the blue donkey`	`'blue' & 'donkey'`	The two words can appear in any order, with any number of words between
`"blue donkey"`	`'blue' <-> 'donkey'`	The words must appear together in the given order
`"the donkey"`	`'donkey'`	Stop words are removed within quotes
`donkey or mule`	`'donkey' \| 'mule'`	Either word will match
`"blue donkey" or "red mule"`	`'blue' <-> 'donkey' \| 'red' <-> 'mule'`	One of the two phrases must match
`blue donkey or mule`	`'blue' & 'donkey' \| 'mule'`	Either “blue” and “donkey” match, or just “mule”
`-mule`	`~'mule'`	“mule” must not match
`donkey -mule`	`'donkey' & !'mule'`	“donkey” matches and “mule” does not
`"blue donkey" -"red mule"`	`'blue' <-> 'donkey' & !( 'red' <-> 'mule' )`	“blue donkey” matches and “red mule” does not

Use websearch in queries

To use the websearch syntax, parse queries with the websearch_to_tsquery() function. This function turns the user-provided query into a tsquery object, ready to match against the tsvector data type. websearch_to_tsquery is described at the end of the “Parsing Queries” documentation section, after the functions for other, less friendly syntaxes.

For example:

postgres=# SELECT 'I am a donkey blue'::tsvector @@ websearch_to_tsquery('english', 'blue donkey');
 ?column?
----------
 t
(1 row)

In Django, use SearchQuery(query, search_type="websearch") to call websearch_to_tsquery. For an example, see DocumentManager.search() in the djangoproject.com source code.

Fin

Thanks to James Turk for adding “websearch” support to Django in Ticket #31088, and Paolo Melchiorre for using it on the Django website in Issue #1204. Without their work, I would not have become aware of this superior syntax.

May your searches be fast and true,

—Adam

Read my book Boost Your Git DX to Git better.

One summary email a week, no spam, I pinky promise.

Related posts:

Tags: django, postgresql