ruby-on-railsrubypostgresqlfull-text-searchpg-search

pg_search exact match of search terms

When trying to find "Harrison Ford" in a document, pg_search will return any text that contains 'Harrison' and 'Ford', for example:

  pg_search_scope :search_by_full_name, :against => [:first_name, :last_name]

People.search_by_full_name("Harrison Ford")

can return:

George Harrison drives a Ford Focus

How can I make sure only exact matches of 'Harrison Ford' will return?

Solution

  • You need to use pg_search normalization, or basically ranking search results in Postgres. I did not even use normalization factor in the following examples:

    SELECT ts_rank_cd(vector,query) as rank 
FROM 
  to_tsvector('simple','George Harrison drives a Ford Focus') as vector,
  to_tsquery('simple','Harrison & Ford') as query;

    Output 1:

       rank    
-----------
 0.0333333
(1 row)

    If you have Harrison and Ford together - rank will be higher:

    SELECT ts_rank_cd(vector,query) as rank
FROM
  to_tsvector('simple','Harrison Ford drives a car') as vector,
  to_tsquery('simple','Harrison & Ford') as query;

    Output 2:

     rank 
------
  0.1
(1 row)

    If you ORDER BY rank DESC all you search results, you will get what you need, because all the search words which are next to each other will the highest rank and will be at the top of your search result list.