[ACCEPTED]-FULLTEXT query with scores/ranks in Postgresql-full-text-search
The Postgres fulltext search is a little 13 different from the MySQL fulltext search. It 12 has a lot more options but can be a bit 11 more difficult to get working the way you 10 like it.
This document tells you how to rank 9 your search results, but I strongly recommend 8 you read the entire fulltext section from 7 the manual to get an idea about what you 6 can do with it: http://www.postgresql.org/docs/current/interactive/textsearch-controls.html#TEXTSEARCH-RANKING
Basically, the equivalent 5 of your query would be this:
SELECT pictures.id, ts_rank_cd(textsearch, 'phrase') AS score
FROM pictures
ORDER BY score DESC
As you can see, this 4 uses textsearch which is something you will have to 3 define yourself. For the short version, read: http://www.postgresql.org/docs/current/interactive/textsearch-tables.html
The 2 query is essentially very simple:
SELECT pictures.id, ts_rank_cd(to_tsvector('english', pictures.title), 'phrase') AS score
FROM pictures
ORDER BY score DESC
But I would 1 strongly recommend adding indexes aswell:
CREATE INDEX pictures_title ON pictures USING gin(to_tsvector('english', title));
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.