to_tsvector for document conversion in PostgreSQL - Mini Project: Build & Apply

Using to_tsvector for Document Conversion in PostgreSQL

📖 Scenario: You work in a library database. You want to prepare book descriptions for fast text searching. PostgreSQL has a tool called to_tsvector that converts text into searchable tokens.

🎯 Goal: Build a simple table with book titles and descriptions. Then, convert the descriptions into searchable text vectors using to_tsvector. This helps find books by keywords quickly.

📋 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.

Create a configuration variable called search_config set to 'english'.

Use to_tsvector with the search_config to convert the description column into a text search vector.

Add a new column description_vector to store the converted vectors.

💡 Why This Matters

🌍 Real World

Libraries, content management systems, and search engines use text search vectors to quickly find relevant documents or records.

💼 Career

Database developers and administrators often optimize text search by converting text columns into tsvector format for fast and efficient searching.

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 Art of Cooking', 'A detailed guide to cooking delicious meals.'), (2, 'Gardening Basics', 'Learn how to grow plants and flowers.'), and (3, 'History of Art', 'Explore the evolution of art through centuries.').

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 rows.

2

Set the text search configuration

Create a variable called search_config and set it to the string 'english'. This will tell PostgreSQL to use English rules for text searching.

PostgreSQL

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

INSERT INTO books (id, title, description) VALUES
(1, 'The Art of Cooking', 'A detailed guide to cooking delicious meals.'),
(2, 'Gardening Basics', 'Learn how to grow plants and flowers.'),
(3, 'History of Art', 'Explore the evolution of art through centuries.');

-- Create a variable called search_config and set it to 'english'
-- Your code here

Need a hint?

Use the psql command \set to create a variable.

3

Convert descriptions using to_tsvector

Write a SQL query that selects id, title, and uses to_tsvector(search_config, description) to convert the description column. Name this converted column description_vector.

PostgreSQL

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

INSERT INTO books (id, title, description) VALUES
(1, 'The Art of Cooking', 'A detailed guide to cooking delicious meals.'),
(2, 'Gardening Basics', 'Learn how to grow plants and flowers.'),
(3, 'History of Art', 'Explore the evolution of art through centuries.');

\set search_config 'english'

-- Select id, title, and to_tsvector(search_config, description) as description_vector
-- Your code here

Need a hint?

Use to_tsvector with the variable search_config and alias the result as description_vector.

4

Add a column to store the text search vector

Alter the books table to add a new column called description_vector of type tsvector. Then update this column for all rows by setting it to to_tsvector(search_config, description).

PostgreSQL

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

INSERT INTO books (id, title, description) VALUES
(1, 'The Art of Cooking', 'A detailed guide to cooking delicious meals.'),
(2, 'Gardening Basics', 'Learn how to grow plants and flowers.'),
(3, 'History of Art', 'Explore the evolution of art through centuries.');

\set search_config 'english'

SELECT id, title, to_tsvector(:search_config, description) AS description_vector FROM books;

-- Alter the books table to add description_vector column of type tsvector
-- Update description_vector with to_tsvector(search_config, description) for all rows
-- Your code here

Need a hint?

Use ALTER TABLE to add the column and UPDATE to fill it.