FULLTEXT query with scores/ranks in Postgresql

Question

Im new to Postgres and I dont know how to translate this MySQL query to postgres:

SELECT pictures.id, MATCH (title, cached_tag_list) AGAINST ('phrase') AS score FROM pictures WHERE MATCH (title, cached_tag_list) AGAINST ('phrase') ORDER BY score DESC;

Answer 1

The Postgres fulltext search is a little different from the MySQL fulltext search. It has a lot more options but can be a bit more difficult to get working the way you like it.

This document tells you how to rank your search results, but I strongly recommend you read the entire fulltext section from the manual to get an idea about what you can do with it: http://www.postgresql.org/docs/8.4/static/textsearch-controls.html#TEXTSEARCH-RANKING

Basically, the equivalent of your query would be this:

SELECT pictures.id, ts_rank_cd(textsearch, 'phrase') AS score
FROM pictures
ORDER BY rank DESC

As you can see, this uses textsearch which is something you will have to define yourself. For the short version, read: http://www.postgresql.org/docs/8.4/static/textsearch-tables.html