Ranking with ts_rank in PostgreSQL

Introduction

Ranking with ts_rank helps you find and order text search results by how well they match your search words.

You want to search a list of articles and show the most relevant ones first.

You have a product catalog and want to rank products by how closely their descriptions match a search term.

You want to highlight the best matching rows in a large text database.

You need to sort search results by relevance instead of just showing all matches equally.

Syntax

PostgreSQL

ts_rank(vector, query [, weights])

vector is a special text search vector made from your text data.

query is the search phrase turned into a text search query.

Examples

This ranks how well the word 'PostgreSQL' matches the text 'PostgreSQL tutorial'.

PostgreSQL

SELECT ts_rank(to_tsvector('english', 'PostgreSQL tutorial'), to_tsquery('english', 'PostgreSQL')) AS rank;

This ranks the phrase 'SQL & databases' against the text 'Learn SQL and databases'.

PostgreSQL

SELECT ts_rank(to_tsvector('english', 'Learn SQL and databases'), to_tsquery('english', 'SQL & databases')) AS rank;

Sample Program

This example creates a table of articles, inserts some sample data, and then searches the body text for 'SQL' and 'database'. It ranks the articles by how well they match and shows the best matches first.

PostgreSQL

CREATE TABLE articles (id SERIAL PRIMARY KEY, title TEXT, body TEXT);

INSERT INTO articles (title, body) VALUES
('PostgreSQL Basics', 'Learn the basics of PostgreSQL database.'),
('Advanced SQL', 'Deep dive into SQL queries and optimization.'),
('Database Tutorial', 'Tutorial on databases and SQL.');

SELECT id, title, ts_rank(to_tsvector('english', body), to_tsquery('english', 'SQL & database')) AS rank
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'SQL & database')
ORDER BY rank DESC;

Important Notes

The ts_rank function returns a number showing match quality; it is not limited to between 0 and 1.

Higher rank means better match.

You can use weights to give more importance to some words.

Summary

ts_rank helps order search results by relevance.

Use to_tsvector to prepare text and to_tsquery for search terms.

Sort results by ts_rank to show best matches first.