postgresql Wildcard search and ranking not working as expected

Question

I have this tsvector column for full-text search of two columns' name and address.

ALTER TABLE public.business_info ADD column search_vector tsvector NULL GENERATED ALWAYS AS (setweight(to_tsvector('simple'::regconfig, COALESCE(name, ''::text)), 'A'::"char") || setweight(to_tsvector('simple'::regconfig, COALESCE(address, ''::text)), 'B'::"char")) STORED

I am trying the below query to find records with full text wild card search

SELECT name,ts_rank_cd(search_vector, to_tsquery('Oklahoma&State:*')) as rank 
from business_info order by rank desc

But I am getting the result as below though I am expecting, records with the name "Oklahoma state" show a higher ranking. Any idea how to rank these results to get the results with a higher ranking on the inputs that the user has given?

Answer 1

If you are interested on higher score in the starting positions of the string, simple define an expression for this part.

Here an example for the first two words

select 
coalesce((string_to_array(name, ' '))[1],' ')||' ' || coalesce((string_to_array(name, ' '))[2],' ')  as name_pfx,
name
from business_info;

name_pfx,           name
Southwest Oklahoma  Southwest Oklahoma State University at Oklahoma State University-OKC

Than define a higher weight for this prefix e.g.

ALTER TABLE  business_info 
ADD column search_vector tsvector NULL GENERATED ALWAYS AS 
 (setweight(to_tsvector('simple'::regconfig, 
       COALESCE((coalesce((string_to_array(name, ' '))[1],' ')||' ' || coalesce((string_to_array(name, ' '))[2],' ')), ''::text)), 'A'::"char") || 
  setweight(to_tsvector('simple'::regconfig, COALESCE(name, ''::text)), 'B'::"char")) stored;

Resut is as expected

SELECT name_pfx||' '||name name,ts_rank_cd(search_vector, to_tsquery('Oklahoma&State:*')) as rank 
from  business_info order by rank desc

"rank",  "name"                                      
2.1714287   Oklahoma State Oklahoma State University - Oklahoma City
2.0714285   Oklahoma State Oklahoma State University at Western Oklahoma
0.93333334  Southwest Oklahoma Southwest Oklahoma State University at Oklahoma State University-OKC
0.90000004  Southwest Oklahoma Southwest Oklahoma State University at Northwest Oklahoma State University-A
0.90000004  Western Oklahoma Western Oklahoma State College at Western Oklahoma State College