CODEWITHSUNDEEP
postgresqllevenshtein-distancefuzzy-searchtsvector
Stephen Baden
Stephen Baden

Reputation: 101

Using Levenshtein function on each element in a tsvector?

I'm trying to create a fuzzy search using Postgres and have been using django-watson as a base search engine to work off of.

I have a field called search_tsv that its a tsvector containing all the field values of the model that I want to search on.

I was wanting to use the Levenshtein function, which does exactly what I want on a text field. However, I dont really know how to run it on each individual element of the tsvector.

Is there a way to do this?

Upvotes: 7

Views: 5392

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658817

Consider the extension pg_trgm instead of levenshtein(). It is faster by orders of magnitude when backed by a GiST index to support a "nearest neighbor" search.

Install the extension once per database:

CREATE EXTENSION pg_trgm;

And use the <-> or % operator. Several related answers have been posted here on SO. Search for pg_tgrm [PostgreSQL] ...

Wild shot at what you may want:

WITH x AS (
    SELECT unnest(string_to_array(trim(strip(
      'fat:2,4 cat:3 rat:5A'::tsvector)::text, ''''), ''' ''')) AS val
    )                                     -- provide ts_vector, extract strings
    , y AS( SELECT 'brat'::text AS term)  -- provide term to match
SELECT val, term
     , (val <-> term) AS trg_dist         -- distance operator
     , levenshtein(val, term) AS lev_dist
FROM   x, y;

Returns:

 val | term | trg_dist | lev_dist
-----+------+----------+----------
 cat | brat |    0.875 |        2
 fat | brat |    0.875 |        2
 rat | brat | 0.714286 |        1

Upvotes: 12

Related Questions