tsvector and tsquery types in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this tsquery match?

Given the following SQL query, what is the result of the match condition?

PostgreSQL

SELECT to_tsvector('english', 'The quick brown fox') @@ to_tsquery('english', 'quick & fox') AS match_result;

A true

B false

C Syntax error

D NULL

💡 Hint

Check if both words 'quick' and 'fox' appear in the tsvector.

The tsvector contains 'quick' and 'fox', and the tsquery requires both with '&', so the match is true.

📝 Syntax

intermediate

Which option correctly creates a tsquery for phrase search?

Which SQL statement correctly creates a tsquery to search for the phrase 'data science'?

A SELECT to_tsquery('english', 'data <-> science');

B SELECT to_tsquery('english', 'data & science');

C SELECT to_tsquery('english', 'data | science');

D SELECT to_tsquery('english', 'data - science');

💡 Hint

Phrase search uses the <-> operator between words.

The <-> operator specifies that 'data' must be immediately followed by 'science', which is a phrase search.

❓ optimization

advanced

How to optimize full-text search on a large text column?

You have a large table with a text column 'content'. Which approach optimizes full-text search performance?

A Create a B-tree index on content

B Create a GIN index on to_tsvector('english', content)

C Create a GIST index on content

D Create a hash index on to_tsquery('english', content)

💡 Hint

Full-text search uses tsvector and GIN indexes for speed.

GIN indexes on tsvector columns speed up full-text search queries efficiently.

🔧 Debug

advanced

Why does this tsquery return no matches?

Given the query below, why does it return no rows? SELECT * FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('simple', 'cats & dogs');

A The '@@' operator is invalid for tsvector and tsquery

B The to_tsvector function is missing a language parameter

C The query syntax is incorrect and causes a runtime error

D The tsquery 'cats & dogs' does not match because 'cats' is stemmed to 'cat' in tsvector

💡 Hint

Check how stemming affects word forms in tsvector and tsquery.

The tsvector stems 'cats' to 'cat' but the tsquery uses 'cats' which does not stem, so no match occurs.

🧠 Conceptual

expert

What is the difference between plainto_tsquery and to_tsquery?

Choose the correct statement about plainto_tsquery and to_tsquery functions in PostgreSQL.

A plainto_tsquery requires explicit operators; to_tsquery parses plain text automatically

B Both functions behave identically and are interchangeable

C plainto_tsquery parses plain text and automatically adds AND operators; to_tsquery requires explicit operators

D plainto_tsquery returns a tsvector; to_tsquery returns a tsquery

💡 Hint

Think about how each function interprets input text.

plainto_tsquery treats input as plain text and combines words with AND; to_tsquery expects explicit tsquery syntax with operators.

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