tsvector and tsquery types in PostgreSQL

Introduction

These types help you search text quickly and smartly in a database. They let you find words or phrases inside big text data easily.

You want to find all articles containing certain keywords fast.

You need to search a product catalog by description or features.

You want to build a search box that finds relevant documents.

You want to filter emails or messages by specific words.

You want to rank search results by how well they match your query.

Syntax

PostgreSQL

tsvector: stores processed text for searching

tsquery: stores search conditions to match against tsvector

tsvector breaks text into words and stores them in a searchable form.

tsquery defines what words or phrases you want to find.

Examples

This converts a sentence into a tsvector for searching.

PostgreSQL

SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');

This creates a tsquery to find rows containing both 'fox' and 'dog'.

PostgreSQL

SELECT to_tsquery('english', 'fox & dog');

This checks if the text contains both 'powerful' and 'database'. It returns true or false.

PostgreSQL

SELECT to_tsvector('english', 'PostgreSQL is a powerful database') @@ to_tsquery('english', 'powerful & database');

Sample Program

This creates a table with text, inserts some rows, and searches for rows containing both 'powerful' and 'database'.

PostgreSQL

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

INSERT INTO articles (content) VALUES
('The quick brown fox jumps over the lazy dog'),
('PostgreSQL is a powerful open-source database'),
('Full text search is very useful');

SELECT id, content
FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'powerful & database');

Important Notes

Use to_tsvector to prepare text for searching.

Use to_tsquery to build search queries with AND (&), OR (|), and NOT (!) operators.

The @@ operator checks if the text matches the search query.

Summary

tsvector stores searchable text.

tsquery defines what to search for.

Use @@ to test if text matches your search.

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