CODEWITHSUNDEEP
pythondjangopostgresqlfull-text-search
rainsurf
rainsurf

Reputation: 41

Partial matches with PostgreSQL full text search

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?

Upvotes: 4

Views: 3527

Answers (1)

bignose
bignose

Reputation: 32279

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 :-)

Upvotes: 4

Related Questions