← Back to TIL

websearch_to_tsquery for Postgres full text search

Postgres' full text search features go a long way towards making it easy to implement a very powerful search system, but it has always been a bit tricky going from user-entered text to a well-constructed tsquery. In Postgres 11 a new function was introduced websearch_to_tsquery which implements all the common search features from engines like google, including quoted phrases and word subtraction.

select websearch_to_tsquery('"This is a phrase" bananas -apples');

-- results in: 'this' <-> 'is' <-> 'a' <-> 'phrase' & 'bananas' & !'apples'