tsvector and tsquery types in PostgreSQL - Step-by-Step Execution

Concept Flow - tsvector and tsquery types

Input Text

↓

Convert to tsvector

↓

Store/Search in tsvector column

↓

Create tsquery

↓

Match tsquery against tsvector

↓

Return matching rows

Text is converted into a searchable vector (tsvector). A query (tsquery) is created to search this vector. Matching rows are returned.

Execution Sample

PostgreSQL

SELECT to_tsvector('english', 'The quick brown fox') AS document_vector;
SELECT to_tsquery('english', 'quick & fox') AS search_query;
SELECT * FROM documents WHERE document_vector @@ to_tsquery('english', 'quick & fox');

Convert text to tsvector, create tsquery, then find rows where the vector matches the query.

Execution Table

Step	Action	Input	Output/Result
1	Convert text to tsvector	'The quick brown fox'	'brown':3 'fox':4 'quick':2
2	Create tsquery	'quick & fox'	'quick' & 'fox'
3	Match tsquery against tsvector	tsvector: 'brown':3 'fox':4 'quick':2, tsquery: 'quick' & 'fox'	TRUE (matches)
4	Match tsquery against tsvector	tsvector: 'brown':3 'fox':4, tsquery: 'quick' & 'fox'	FALSE (no match)
5	Exit	No more rows	Query complete

💡 All rows checked; query returns only rows where tsquery matches tsvector

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
tsvector	NULL	'brown':3 'fox':4 'quick':2	'brown':3 'fox':4 'quick':2	'brown':3 'fox':4 'quick':2	'brown':3 'fox':4 'quick':2
tsquery	NULL	NULL	'quick' & 'fox'	'quick' & 'fox'	'quick' & 'fox'

Key Moments - 2 Insights

Why does 'quick & fox' match the tsvector but 'quick & rabbit' does not?

Because the tsvector contains 'quick' and 'fox' but does not contain 'rabbit'. The match requires all terms in tsquery to be present in tsvector (see execution_table rows 3 and 4).

What does the numbers after words in tsvector mean, like 'quick':2?

They represent the position of the word in the original text. This helps with phrase searching but does not affect simple matching (see execution_table row 1).

Visual Quiz - 3 Questions

Look at the execution_table row 1. What is the position of the word 'fox' in the tsvector?

A 4

B 2

C 3

D 1

💡 Hint

Check the output column in row 1 for 'fox': its position is after the colon.

At which step does the tsquery get created?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the Action column in the execution_table to find when tsquery is created.

If the tsvector does not contain the word 'quick', what will be the result of matching with tsquery 'quick & fox'?

A TRUE

B Error

C FALSE

D Partial match

💡 Hint

Refer to execution_table row 4 where missing a word causes the match to be FALSE.

Concept Snapshot

tsvector stores processed text as lexemes with positions.
tsquery represents search conditions using lexemes.
Use @@ operator to match tsquery against tsvector.
tsvector is created with to_tsvector(text).
tsquery is created with to_tsquery(query).
Matching returns TRUE if tsquery conditions are met in tsvector.

Full Transcript

This visual execution shows how PostgreSQL processes text search using tsvector and tsquery types. First, input text is converted into a tsvector, which breaks text into lexemes with their positions. Then, a tsquery is created representing the search terms and conditions. The tsquery is matched against the tsvector using the @@ operator. The execution table traces each step: converting text, creating query, matching, and results. Variables track the state of tsvector and tsquery through the steps. Key moments clarify common confusions like why some queries match and others don't, and what the position numbers mean. The quiz tests understanding of positions, creation steps, and matching logic. The snapshot summarizes the key points for quick reference.

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