Exact match for pg_search searches

Question

If you try to find "Harrison Ford" in the document, pg_search will return any text containing "Harrison" and "Ford", for example:

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

People.search_by_full_name ("Harrison Ford")

may return:

George Harrison drives Ford Focus

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

Answer 1

You need to use pg_search normalization or basically a ranking of search results in Postgres. I did not even use the normalization coefficient 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, the 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 search results, you will get what you need, because all search words that are next to each other will have the highest rank and will be at the top of the list of search results.