AdvancedString Functions

TO_TSVECTOR + TO_TSQUERY: full-text search

The query

SQL

-- Full text search on product names and descriptions
SELECT
  name,
  TS_RANK(TO_TSVECTOR('english', name), query) AS rank
FROM products,
  TO_TSQUERY('english', 'wireless & bluetooth') query
WHERE TO_TSVECTOR('english', name) @@ query
ORDER BY rank DESC;

Tested against PostgreSQL 16

Note

“PostgreSQL full-text search: tsvector is indexed word list, tsquery is search query. @@ is match operator.

Tables referenced

products6 cols