Fuzzy searching through multiple fields in postgreSQL

by

The question:

I’ve to do fuzzy search on multiple fields (in an attempt to create something like autocomplete similar to product search in amazon).
I tried this through ElasticSearch but was wondering if there’s something equivalent to it in postgreSQL.

Here’s sample code for elasticsearch: (both the fields, title and description, are index as type: text)

GET index_name/_search
{
    "query": {
          "bool": {
                "must": [
                      {
                           "multi_match": {
                                 fields: ["description", "title"],
                                 query: "postgres",
                                 fuzziness: 1
                           }
                      }
                ]
          }
    }
}

I’ve tried the same using pg_tram in postgreSQL, it worked for one field with similarity() (% operator) but I don’t know how to extend this on multiple fields.

This is what I did in postgreSQL, not sure if it’s good way though:

select * from table t
   where similarity("title", "postgres") > 0.5;

select * from table t
   where similarity("title", "postgres") > 0.5 OR similarity("description", "postgres") > 0.5;

Also is there any way to introduce fuzziness in ts_vector (FTS) query ?
Will appreciate any help/guidance in this context.
P.S: let me know if my description is missing something.

Thanks 🙂

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

You could use the word similarity operator <%:

SELECT ... FROM tab
WHERE 'postgres' <% concat(title, ' ', description);

To speed that up, you can create a GIN index on that expression:

CREATE INDEX ON tab USING gin (concat(title, ' ', description) gin_trgm_ops);

You can adjust the parameter pg_trgm.word_similarity_threshold to get the desired sensitivity.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment