[ACCEPTED]-FULLTEXT query with scores/ranks in Postgresql-full-text-search

Accepted answer
Score: 12

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