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/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));