tsvector and tsquery types in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the tsvector type used for in PostgreSQL?

tsvector stores pre-processed text for full-text search. It breaks text into lexemes (words) and removes duplicates, making search faster.

beginner

What does the tsquery type represent in PostgreSQL?

tsquery represents a search query for full-text search. It contains lexemes and operators to find matching tsvector entries.

intermediate

How do tsvector and tsquery work together?

tsvector holds searchable text data, and tsquery holds the search pattern. PostgreSQL matches tsquery against tsvector to find relevant rows.

beginner

What PostgreSQL operator is used to match tsquery against tsvector?

The @@ operator is used. For example, tsvector_column @@ tsquery_expression returns true if the query matches the text.

beginner

How can you convert plain text to tsvector in PostgreSQL?

Use the to_tsvector('language', 'text') function. It processes the text into lexemes for searching.

Which PostgreSQL type stores processed searchable text for full-text search?

A varchar

B tsquery

C text

D tsvector

tsvector stores processed text for searching, while tsquery stores the search query.

What operator is used to check if a tsquery matches a tsvector?

A @@

B ~~

C =

D LIKE

The @@ operator matches tsquery against tsvector.

Which function converts plain text into a tsvector?

A text_to_vector()

B to_tsquery()

C to_tsvector()

D vectorize_text()

to_tsvector() converts text to tsvector. to_tsquery() creates a tsquery.

What does tsquery contain?

A Search query with lexemes and operators

B Raw text data

C Database schema

D Table indexes

tsquery holds the search pattern with words and logical operators.

Which of these is true about tsvector?

A It stores unprocessed text

B It stores processed lexemes for searching

C It is used to write queries

D It is a type of index

tsvector stores processed lexemes, not raw text.

Explain how tsvector and tsquery types work together in PostgreSQL full-text search.

Think about how text is prepared and then searched.

Describe the purpose of the to_tsvector and to_tsquery functions.

One prepares text to be searched, the other prepares the search pattern.

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