2024-01

Tags: PostgreSQL Vector websearch

2024-01-03 PostgreSQL: Full text search with the “websearch” syntax by Adam Johnson

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.

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)

Django example

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

Note

Postgres 11 adds support for a more natural query syntax as an alternative to the existing ‘raw’ that has boolean operators.

Supporting it in postgres.search is pretty trivial and makes things much easier for people wishing to power user-facing search without writing their own parser.

Source: https://code.djangoproject.com/ticket/31088

#​536 — 2024-01-03

Happy New Year!

Every year has been more jam-packed than the last when it comes to Postgres, so we’re looking forward to seeing what happens in 2024.

First, though, we want to take an opportunity to reflect back on what readers enjoyed most in 2023, just in case you missed anything significant :-)

3: Vectors are the New JSON in Postgres

Jonathan’s assertion is only becoming more true in 2024 than it was in June 2023.

Machine learning and LLM embeddings have made vectors the data structure du jour, and Postgres’s support for them is growing by the month (though mostly by way of extensions, for now, such as pgvector).