3

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?

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.