patternsqlMinor
Postgres full text search with better match
Viewed 0 times
fullsearchpostgreswithtextmatchbetter
Problem
I have this scripts for full text searching in my postgres database:
function for unaccent concat:
and this script is for full text search:
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:
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?
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?
Solution
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:
Where the
There are other ts_rank normalizations in the link
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 1Where the
ts_rank(..., ..., 1) divides the rank by 1 + the logarithm of the document lengthThere are other ts_rank normalizations in the link
Code Snippets
select
*
from geocode.addresses
where
...
order by
ts_rank( to_tsvector(...), plainto_tsquery(...), 1) desc
limit 1Context
StackExchange Database Administrators Q#164914, answer score: 2
Revisions (0)
No revisions yet.