postgresqlsearchsimilaritytrigram

Postgresql - Similarity with trigram (pg_trgm)

I am currently implementing a search functionality on my app. I have an user table which contains an username and full_name fields. I want to search the users with the best similarity (from username or full_name). I searched a lot on stackoverflow and I found out a very performatic implementation: https://stackoverflow.com/a/44856792/5979369

I used this code and I created this search query:

SELECT username, email, full_name
 , similarity(username  , 'mar') AS s_username
 , similarity(full_name , 'mar') AS s_full_name
 , row_number() OVER () AS rank  -- greatest similarity first
FROM   user
WHERE     (username || ' ' || full_name) %   'mar'  -- !!
ORDER  BY (username || ' ' || full_name) <-> 'mar'  -- !!
LIMIT  20;

I have an user which username is mariazirita but when I use this query searching by mar it doesn't return nothing. If I search for maria instead it already returns the user.

What can I do to improve this query to also return the user when I search for mar or ma?

Thank you

Solution

  • The problem here is the % operator. It will return TRUE only if the similarity exceeds the pg_trgm.similarity_threshold parameter, which defaults to 0.3.

    SELECT similarity('mariazirita', 'mar');

 similarity 
════════════
 0.23076923
(1 row)

SELECT similarity('mariazirita', 'maria');

 similarity 
════════════
  0.3846154
(1 row)

    So you can either lower the threshold or remove the condition with % from the query.