I am trying to implement fuzzy text search with PostgreSQL on a Nodejs/Express server. I've got the query working using the "ILIKE" operator but it is very important that this query be optimised and I read that using the similarity operator "%" here would force use of the GIN index created on the title column using gin_trgm_ops. But for some reason, the query does not return any values using the "%" operator. I've tried escaping the string then concatenation which received some syntax errors. Any pointers would be appreciated.
This version is not working:
var query = ['SELECT title, user FROM reviews']
query.push("WHERE (title % $1) AND NOT (user = $2) AND ((approved_date IS NULL) OR (approved_date < now()))")
query.push('ORDER BY similarity(title,$1) DESC LIMIT 99')
This version using ILIKE is working:
var query = ['SELECT title, user FROM reviews']
query.push("WHERE title ILIKE '%' || $1 || '%' AND NOT (user = $2) AND ((approved_date IS NULL) OR (approved_date < now()))")
query.push('ORDER BY similarity(title,$1) DESC LIMIT 99')
Also, am I to understand from the documentation that using the distance operator <-> will not work properly here because I am using a GIN index rather than GIST? Thanks.