tsvector and tsquery types in PostgreSQL - Mini Project: Build & Apply

Full-Text Search with tsvector and tsquery in PostgreSQL

📖 Scenario: You are building a simple search feature for a small library database. The library stores book titles and descriptions. You want to enable users to quickly find books by searching keywords in the descriptions.

🎯 Goal: Create a PostgreSQL table with book data, add a tsvector column for full-text search, configure a search query using tsquery, and perform a search to find matching books.

📋 What You'll Learn

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

Insert three specific book records with given titles and descriptions

Add a tsvector column named search_vector to store searchable text

Update search_vector with the combined text of title and description using to_tsvector

Write a query using to_tsquery to search for books matching the keyword 'adventure'

💡 Why This Matters

🌍 Real World

Full-text search is used in many applications like search engines, document management systems, and e-commerce sites to quickly find relevant text data.

💼 Career

Understanding tsvector and tsquery types is essential for database developers and backend engineers working with PostgreSQL to implement efficient search features.

1

Create the books table and insert data

Create a table called books with columns id (integer primary key), title (text), and description (text). Then insert these three rows exactly: (1, 'The Lost World', 'An exciting adventure in a mysterious land'), (2, 'Cooking 101', 'Basic recipes for beginners'), and (3, 'Space Odyssey', 'A journey through the stars and beyond').

PostgreSQL

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

Need a hint?

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

2

Add a tsvector column for full-text search

Add a new column named search_vector of type tsvector to the books table. This column will store the searchable text data.

PostgreSQL

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

INSERT INTO books (id, title, description) VALUES
(1, 'The Lost World', 'An exciting adventure in a mysterious land'),
(2, 'Cooking 101', 'Basic recipes for beginners'),
(3, 'Space Odyssey', 'A journey through the stars and beyond');

-- Add a tsvector column named search_vector
-- Your code here

Need a hint?

Use ALTER TABLE or add the column directly in the CREATE TABLE statement.

3

Update search_vector with combined text using to_tsvector

Update the search_vector column for all rows by combining the title and description columns using to_tsvector('english', title || ' ' || description).

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    description TEXT,
    search_vector tsvector
);

INSERT INTO books (id, title, description) VALUES
(1, 'The Lost World', 'An exciting adventure in a mysterious land'),
(2, 'Cooking 101', 'Basic recipes for beginners'),
(3, 'Space Odyssey', 'A journey through the stars and beyond');

-- Update search_vector with combined title and description
-- Your code here

Need a hint?

Use UPDATE to fill the search_vector column with combined text.

4

Search books using to_tsquery for the keyword 'adventure'

Write a SELECT query to find all columns from books where search_vector matches the tsquery created by to_tsquery('english', 'adventure'). Use the @@ operator for matching.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    description TEXT,
    search_vector tsvector
);

INSERT INTO books (id, title, description) VALUES
(1, 'The Lost World', 'An exciting adventure in a mysterious land'),
(2, 'Cooking 101', 'Basic recipes for beginners'),
(3, 'Space Odyssey', 'A journey through the stars and beyond');

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

-- Select all columns where search_vector matches to_tsquery('adventure')
-- Your code here

Need a hint?

Use the @@ operator to match tsvector with tsquery.