Fulltext search combined with fuzzysearch in PostgreSQL

As the documentation on parsing states:

...plainto_tsquery will not recognize tsquery operators, weightlabels, or prefix-match labels in its input...

plainto_tsquery and phraseto_tsquery are convenience functions which make it easier to search by a full string, but they don't support all of the features. 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, queryWHERE (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.