Ranking with ts_rank in PostgreSQL - Mini Project: Build & Apply

Ranking Search Results with ts_rank in PostgreSQL

📖 Scenario: You are building a simple search feature for a small library database. Users can search for books by keywords in the title and description. You want to rank the search results by how well they match the search terms.

🎯 Goal: Create a PostgreSQL table with book data, set up a full-text search vector, write a query that uses ts_rank to rank the books by relevance to a search query, and select the ranked results.

📋 What You'll Learn

Create a table called books with columns id, title, and description

Insert 3 specific book records with given titles and descriptions

Add a column search_vector that combines title and description for full-text search

Write a query that uses to_tsquery with the search term 'data & science'

Use ts_rank to rank the results by relevance

Select id, title, and the rank as rank ordered by rank descending

💡 Why This Matters

🌍 Real World

Full-text search ranking is used in search engines, library catalogs, and any application where users search text data.

💼 Career

Knowing how to rank search results with ts_rank is valuable for database developers and backend engineers working with PostgreSQL.

1

Create the books table and insert data

Create a table called books with columns id as serial primary key, title as text, and description as text. Then insert these three rows exactly: (1, 'Data Science Handbook', 'A comprehensive guide to data science techniques.'), (2, 'Cooking Basics', 'Learn the fundamentals of cooking delicious meals.'), and (3, 'Science Experiments', 'Fun and educational science experiments for kids.').

PostgreSQL

-- Create the books table and insert the three rows below
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Add a search_vector column for full-text search

Add a new column called search_vector of type tsvector to the books table. Then update this column by combining the title and description columns using to_tsvector('english', title || ' ' || description).

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    description TEXT
);

INSERT INTO books (id, title, description) VALUES
(1, 'Data Science Handbook', 'A comprehensive guide to data science techniques.'),
(2, 'Cooking Basics', 'Learn the fundamentals of cooking delicious meals.'),
(3, 'Science Experiments', 'Fun and educational science experiments for kids.');

-- Add search_vector column and update it below
-- Your code here

Need a hint?

Use ALTER TABLE to add the column, then UPDATE to fill it.

3

Write a query using to_tsquery and ts_rank

Write a SELECT query that searches the books table using to_tsquery('english', 'data & science') to match the search_vector. Use ts_rank(search_vector, to_tsquery('english', 'data & science')) to calculate a rank and select it as rank. Select id, title, and rank.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    description TEXT
);

INSERT INTO books (id, title, description) VALUES
(1, 'Data Science Handbook', 'A comprehensive guide to data science techniques.'),
(2, 'Cooking Basics', 'Learn the fundamentals of cooking delicious meals.'),
(3, 'Science Experiments', 'Fun and educational science experiments for kids.');

ALTER TABLE books ADD COLUMN search_vector tsvector;

UPDATE books SET search_vector = to_tsvector('english', title || ' ' || description);

-- Write the SELECT query with ts_rank below
-- Your code here

Need a hint?

Use @@ to filter rows matching the query and ts_rank to rank them.

4

Order the results by rank descending

Add an ORDER BY rank DESC clause to the query to show the most relevant books first.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    description TEXT
);

INSERT INTO books (id, title, description) VALUES
(1, 'Data Science Handbook', 'A comprehensive guide to data science techniques.'),
(2, 'Cooking Basics', 'Learn the fundamentals of cooking delicious meals.'),
(3, 'Science Experiments', 'Fun and educational science experiments for kids.');

ALTER TABLE books ADD COLUMN search_vector tsvector;

UPDATE books SET search_vector = to_tsvector('english', title || ' ' || description);

SELECT id, title, ts_rank(search_vector, to_tsquery('english', 'data & science')) AS rank
FROM books
WHERE search_vector @@ to_tsquery('english', 'data & science')

-- Add ORDER BY clause below
-- Your code here

Need a hint?

Use ORDER BY rank DESC to sort results from highest to lowest rank.