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.