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 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.