FULLTEXT query with scores/ranks in Postgresql
full-text-search, mysql, postgresql
Solution
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/current/interactive/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 score 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/current/interactive/textsearch-tables.html
The 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 strongly recommend adding indexes aswell:
CREATE INDEX pictures_title ON pictures USING gin(to_tsvector('english', title));
Problem
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; ```