Hi I'd like to define a query for PostgreSQL full-text search such that the results matching with all the terms are ranked higher while results matching with only some of the terms are also retrieved.
Example:
query: tree wood
I'd like to get results including both words in higher ranks. However, I'd also like to get results including only tree or wood.
I tried to use plainto_tsquery ('tree | wood') but this does not prioritize the records including all query terms.
I have a database column typed ts_vector and I'm doing the search on this column.
Thanks in advance for your answers.
Solution: I was able to achieve what I want after sorting the results by rank explicitly with something like:
select * from table, to_tsquery('english', 'wood | tree') query where vector_column @@ query order by ts_rank_cd(search_index, query) desc;
The built in ranking functions ts_rank and ts_rank_cd should do this automatically. Note that the query fed to the ranking functions should be the one with the |, not &.
Note that it will probably be faster to first do the & query, and then do the | query only if the first one found no rows. But of course then you don't get any | rows if there is at least one & row, so you have to decide whether that is acceptable.