Full text search in postgres

I just learnt about tsvector and tsquery in Postgres, which are used for full-text search.

This gives me a tsvector document that I can then use to search against.

SELECT 
       to_tsvector(post.title) ||
       to_tsvector(post.content) ||
       to_tsvector(author.name) ||
       to_tsvector(coalesce((string_agg(tag.name, ' ')), '')) as document
FROM post
JOIN author on author.id = post.author_id
JOIN posts_tags on posts_tags.post_id = post.id
JOIN tag on tag.id = posts_tags.tag_id
GROUP BY post.id, author.id;

Then I can use the @@ operator to match a tsquery against the tsvector.

SELECT pid, p_title
FROM (
    SELECT 
           post.id as pid,
           post.title as p_title,
           to_tsvector(post.title) ||
           to_tsvector(post.content) ||
           to_tsvector(author.name) ||
           to_tsvector(coalesce((string_agg(tag.name, ' ')), '')) 
    as document FROM post
    JOIN author on author.id = post.author_id
    JOIN posts_tags on posts_tags.post_id = post.id
    JOIN tag on tag.id = posts_tags.tag_id
    GROUP BY post.id, author.id
) p_search
WHERE p_search.document @@ to_tsquery('gym & work');

There is also language support, but I didn't look much into that.

Another feature is ranking the results using ts_rank function and settings weights to different parts of the document.

SELECT pid, p_title
FROM (
    SELECT 
           post.id as pid,
           post.title as p_title,
           setweight(to_tsvector(post.title), 'A') ||
           setweight(to_tsvector(post.content), 'B') ||
           setweight(to_tsvector(author.name), 'C') ||
           setweight(to_tsvector(coalesce((string_agg(tag.name, ' ')), '')), 'B')
    as document FROM post
    JOIN author on author.id = post.author_id
    JOIN posts_tags on posts_tags.post_id = post.id
    JOIN tag on tag.id = posts_tags.tag_id
    GROUP BY post.id, author.id
) p_search
WHERE p_search.document @@ to_tsquery('gym & work');
ORDER BY ts_rank(p_search.document, to_tsquery('gym & work')) DESC;