tsvector and tsquery types in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a tsvector column named 'document_vector' from the 'content' column.

PostgreSQL

SELECT to_tsvector([1]) FROM documents;

Drag options to blanks, or click blank then click option'

Acontent

B'content'

Cdocument

D'english'

Attempts:

3 left

💡 Hint

Common Mistakes

Putting the column name in quotes, which makes it a string literal.

Using the language name instead of the column name.

The to_tsvector function converts the text in the content column into a tsvector type for full-text search.

2fill in blank

medium

Complete the code to create a tsquery that searches for the word 'database'.

PostgreSQL

SELECT to_tsquery([1]);

Drag options to blanks, or click blank then click option'

A'english', 'database'

B'database'

C'database & english'

D'english & database'

Attempts:

3 left

💡 Hint

Common Mistakes

Including the language name inside the query string.

Using multiple words without operators.

The to_tsquery function takes a query string like 'database' to create a tsquery for searching.

3fill in blank

hard

Fix the error in the code to match documents containing 'postgres' or 'sql'.

PostgreSQL

SELECT * FROM documents WHERE document_vector @@ to_tsquery([1]);

Drag options to blanks, or click blank then click option'

A'postgres sql'

B'postgres & sql'

C'postgres | sql'

D'postgres OR sql'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '&' which means AND instead of OR.

Writing 'OR' as a word instead of using the symbol '|'.

The | operator means OR in tsquery syntax, so 'postgres | sql' matches documents containing either word.

4fill in blank

hard

Fill both blanks to create a tsquery that matches documents containing 'data' but not 'backup'.

PostgreSQL

SELECT * FROM documents WHERE document_vector @@ to_tsquery([1] [2] 'backup');

Drag options to blanks, or click blank then click option'

A'data'

B'!data'

C'&'

D'|'

Attempts:

3 left

💡 Hint

Common Mistakes

Using OR | instead of AND &.

Placing NOT operator incorrectly.

The & operator means AND, so 'data & !backup' matches documents with 'data' but not 'backup'.

5fill in blank

hard

Fill both blanks to create a dictionary comprehension that maps words to their lexemes using to_tsvector and filters lexemes longer than 3 characters.

PostgreSQL

SELECT jsonb_object_agg(word-> lexeme) FROM (SELECT unnest(string_to_array(content, ' ')) AS word, unnest(to_tsvector([1])::text[]) AS lexeme FROM documents) AS sub WHERE length(lexeme) [2] 3;

Drag options to blanks, or click blank then click option'

A->

Bcontent

C>

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong operators like -> or < instead of >.

Using string literals instead of column names.

The operator -> accesses JSON object fields, content is the text column, and > filters lexemes longer than 3 characters.

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