Deviopath › PostgreSQL

Search Articles by Relevance

Hard · Full-Text Search

Full-text search turns text into a tsvector of normalized words (to_tsvector('english', text)) and a search into a tsquery (to_tsquery('english', 'a & b')). @@ tests for a match, and ts_rank(vector, query) scores it. The English config stems words, so "indexes" matches "index" and "queries" matches "query". setweight(vector, 'A') marks words as more important.

Search the articles for index & query. Use this document for each article:

setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B')

Return id, title and rank (ts_rank of the document and the query, cast to numeric and rounded to 3 decimals) for the matching articles, sorted by rank descending, then id.

Next: Rolling 7-Day Total