Feb 2019 - #database #postgres #sql

Fuzzy Full-Text Search in PostgreSQL

Use to_tsquery instead which accepts the full search syntax:

SELECT *
FROM test, to_tsquery('testing:* & 123:*') as q
WHERE (tsv @@ q)

This function also requires you to normalize the search query in the same way you normalize the text you're searching by using to_tsvector, but that's pretty easy with some string functions:

SELECT string_agg(lexeme || ':*', ' & ' order by positions)
FROM unnest(to_tsvector('testing 123'))

This basically gets the individual tokens from to_tsvector, appends :* to each, then joins them with & to create a single string. The example above takes testing 123 and produces testing:* & 123:* which you can then use directly with to_tsquery to get fuzzy matching with the normalization intact.

You can combine it all together into a CTE to make it simple:

WITH search AS (
    SELECT to_tsquery(string_agg(lexeme || ':*', ' & ' order by positions)) AS query
    FROM unnest(to_tsvector('enter your search query here'))
)
SELECT test.*
FROM test, query
WHERE (test.tsv @@ search.query)

This assumes that the table has a tsv column of datatype tsquery which is pregenerated instead of creating it on every query (which is much slower). PG12+ supports generated columns which can keep this updated automatically.