Postgres websearch in Django 3.1

python
django
Published

June 17, 2020

Earlier this week, Django 3.1 beta 1 was released. While the big news is the addition of asynchronous views and middleware, there’s a tiny feature I managed to get added that I wanted to bring a bit of attention to as it makes writing apps that use Postgres’ full text search a little easier & nicer.

It’s a small line in the changelog, but the SearchQuery constructor now accepts the argument search_type="websearch". This allows you to write code like:

Bill.objects.filter(
  search_vector=SearchQuery(
    "'transportation funding' OR 'transit funding' -highway",
    search_type="websearch",
    config="english"
  )
)

Or take similarly human-formatted search queries from users and present results on your site with minimal fuss. If you’re already using Postgres ≥ 11 this will be available to you as soon as you upgrade to Django 3.1, if you aren’t familiar with Postgres’ full text search it might be helpful to read on for a bit of background.

Enter websearch_to_tsquery

As of Postgres 11, there is a new function available, websearch_to_tsquery. The docs sum it up well:


websearch_to_tsquery creates a tsquery value from query text using an alternative syntax in which simple unformatted text is a valid query. Unlike plainto_tsquery and phraseto_tsquery, it also recognizes certain operators. Moreover, this function should never raise syntax errors, which makes it possible to use raw user-supplied input for search. The following syntax is supported:

  • unquoted text: text not inside quote marks will be converted to terms separated by & operators, as if processed by plainto_tsquery.
  • “quoted text”: text inside quote marks will be converted to terms separated by <-> operators, as if processed by phraseto_tsquery.
  • OR: logical or will be converted to the | operator.
  • -: the logical not operator, converted to the ! operator.

(via Controlling Text Search, Postgres 11)


Those rules are pretty useful, pretty much what most people would need if they were exposing search to their users. This was essentially the missing feature from Postgres’ search before Postgres 11. Perhaps most important is the fact that it is safe to pass user input into this function, whereas passing it to to_tsquery and its cousins could often lead to a syntax error due to their peculiar syntax and rigid parsing.

This means we can write searches like "ham sandwich" OR "turkey sandwich" -mustard in a search box on our site, and pass it through to Postgres and have it just work!

So that’s where that line of Django code from the beginning of this article comes in. By passing search_type="websearch" to SearchQuery, you can use this powerful Postgres feature. This will be available on Django ≥ 3.1 and only if you’re on Postgres ≥ 11.