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

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

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

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

Hint

Use the @@ operator to match tsvector with tsquery.

Practice

1. What does the tsvector type represent in PostgreSQL full-text search?

easy

A. A table storing user data

B. A searchable document converted into lexemes

C. A function to rank search results

D. A query pattern to search text

Solution

1. Step 1: Understand tsvector role

   tsvector stores processed text as lexemes for searching.

2. Step 2: Differentiate from tsquery

   tsquery is the search pattern, not the stored text.

3. Final Answer:

   A searchable document converted into lexemes -> Option B

4. Quick Check:

   tsvector = searchable text [OK]

Hint: Remember: tsvector stores text, tsquery stores search pattern [OK]

Common Mistakes:

• Confusing tsvector with tsquery
• Thinking tsvector is a function
• Assuming tsvector stores raw text

2. Which of the following is the correct syntax to check if a tsvector matches a tsquery in PostgreSQL?

easy

A. tsvector = tsquery

B. tsquery @@ tsvector

C. tsvector @@ tsquery

D. tsvector LIKE tsquery

Solution

1. Step 1: Recall operator for matching

   The @@ operator tests if tsvector matches tsquery.

2. Step 2: Confirm operand order

   The syntax is tsvector @@ tsquery, not reversed or using other operators.

3. Final Answer:

   tsvector @@ tsquery -> Option C

4. Quick Check:

   Match operator = @@ [OK]

Hint: Use tsvector @@ tsquery to test matches [OK]

Common Mistakes:

• Reversing operands in @@ operator
• Using = or LIKE instead of @@
• Confusing tsquery and tsvector roles

3. Given the table documents with a column content_tsv of type tsvector, what will this query return?

SELECT id FROM documents WHERE content_tsv @@ to_tsquery('cat & dog');

medium

A. Rows where content contains both 'cat' and 'dog'

B. Rows where content contains either 'cat' or 'dog'

C. Rows where content contains the exact phrase 'cat dog'

D. Rows where content contains 'cat' but not 'dog'

Solution

1. Step 1: Understand to_tsquery('cat & dog')

   The & operator means AND, so it searches for both words.

2. Step 2: Apply @@ operator

   The query returns rows where content_tsv matches both 'cat' and 'dog'.

3. Final Answer:

   Rows where content contains both 'cat' and 'dog' -> Option A

4. Quick Check:

   AND operator & means both words [OK]

Hint: Use & in tsquery for AND search [OK]

Common Mistakes:

• Thinking & means OR
• Assuming phrase match instead of AND
• Confusing tsquery syntax operators

4. Identify the error in this PostgreSQL query:

SELECT * FROM articles WHERE to_tsquery('dog | cat') @@ content_tsv;

medium

A. The operands of @@ are reversed; tsvector must be on the left

B. The operator | is invalid in tsquery

C. to_tsquery requires two arguments

D. content_tsv must be cast to text before matching

Solution

1. Step 1: Check @@ operator usage

   The left operand must be tsvector, right operand tsquery.

2. Step 2: Identify operand order in query

   The query has to_tsquery('dog | cat') on left (tsquery), which is incorrect.

3. Final Answer:

   The operands of @@ are reversed; tsvector must be on the left -> Option A

4. Quick Check:

   tsvector @@ tsquery order matters [OK]

Hint: Always put tsvector left, tsquery right in @@ [OK]

Common Mistakes:

• Swapping operands in @@ operator
• Misunderstanding tsquery syntax
• Casting tsvector to text unnecessarily

5. You want to find rows in table notes where the content column contains the word 'apple' but not 'pie'. Which tsquery expression correctly achieves this?

SELECT * FROM notes WHERE to_tsvector(content) @@ to_tsquery( ? );

hard

A. 'apple ! pie'

B. 'apple | !pie'

C. 'apple & pie'

D. 'apple & !pie'

Solution

1. Step 1: Understand tsquery operators

   & means AND, ! means NOT, | means OR.

2. Step 2: Construct query for 'apple' AND NOT 'pie'

   The correct expression is 'apple & !pie' to include 'apple' but exclude 'pie'.

3. Final Answer:

   'apple & !pie' -> Option D

4. Quick Check:

   AND + NOT = & ! [OK]

Hint: Use & for AND and ! for NOT in tsquery [OK]

Common Mistakes:

• Using | instead of & for AND
• Omitting & between terms
• Incorrect spacing or missing operators