Postgresql – Postgres full text search with better match

full-text-searchpostgresql

I have this scripts for full text searching in my postgres database:

function for unaccent concat:

CREATE OR REPLACE FUNCTION f_concat_ws_unaccent(text, VARIADIC text[])
RETURNS text LANGUAGE sql IMMUTABLE AS 'SELECT unaccent(array_to_string($2, $1)

and this script is for full text search:

SELECT * FROM geocode.addresses
WHERE  to_tsvector('simple', f_concat_ws_unaccent(' ', country, city,street, housenumber, postcode))
    @@ plainto_tsquery('simple', 'some address') limit 1;

Can you help mi edit this select or create some function, which return result with the best match. For example in my database exist 2 cities. First city called CityA and second city is composite from 2 words, where one word is same as name first city .. for example Upper CityA. When I execute my script like this:

SELECT * FROM geocode.addresses
WHERE  to_tsvector('simple', f_concat_ws_unaccent(' ', country, city,street, housenumber, postcode))
    @@ plainto_tsquery('simple', 'CityA') limit 1;

I need result with the best match row, but scipt return my Upper CityA row instead CityA.
Any ideas how can I edit this for required result?

Best Answer

You want to sort by ts_rank() or ts_rank_cd()

By default, those terms would give the same ranks, but you can normalize by length

Try:

select
  *
from geocode.addresses
where
  ...
order by 
  ts_rank( to_tsvector(...), plainto_tsquery(...), 1) desc
limit 1

Where the ts_rank(..., ..., 1) divides the rank by 1 + the logarithm of the document length

There are other ts_rank normalizations in the link

Related Question