Partial matches with PostgreSQL full text search

Question

Django's documentation for the PostgreSQL full text search uses the following example:

>>> Entry.objects.filter(body_text__search='Cheese')
[<Entry: Cheese on Toast recipes>, <Entry: Pizza Recipes>]

I would like to use this, but to get the same results by searching for only part of the word. Currently this results in no results:

>>> Entry.objects.filter(body_text__search='Chee')
[]

Basically I would like to get the same results that I would get using body_text__icontains. I would like to use full text search so that after I get this working I can take advantage of things such as reusable search vectors, weighting queries and ranking.

Is there some way to get results with partial word queries?

Lauber

Related discussion on use of prefix matches with wildcard: #45110567 – Aristate

Answer 1

The django.contrib.postgres.search module is implemented on PotgreSQL's full-text search engine.

Specifically, it turns the text values into PostgreSQL “tsvector” values, and turns your search term into a PostgreSQL “tsquery” value. So, if you're using this feature, you should learn about how to search using tsquery.

The example you give is illustrated in PostgreSQL like this:

=> SELECT 'Cheese on Toast recipes'::tsvector @@ 'Cheese'::tsquery;
 ?column? 
----------
 t
(1 row)

=> SELECT 'Cheese on Toast recipes'::tsvector @@ 'Chees'::tsquery;
 ?column? 
----------
 f
(1 row)

So, a tsquery of 'Chees' doesn't match the text. Django is telling you the right thing.

An example of a tsquery that does match is 'Chees:*':

=> SELECT 'Cheese on Toast recipes'::tsvector @@ 'Chees:*'::tsquery;
 ?column? 
----------
 t
(1 row)

The larger answer is: You'll need to learn about how to form full-text search queries the way PostgreSQL expects them, if you want to use this feature correctly :-)

Ununa

Actually, the django.contrib.postgres.search module will produce the following query: SELECT to_tsvector('Cheese on Toast recipes') @@ plainto_tsquery('Cheese'); It is very different from what you wrote. – Mauer

Actually, I didn't present any examples of what Django produces, so your “actually” doesn't contradict what I wrote :-) The examples presented are to illustrate how to answer the question in a PostgreSQL session. – Ununa