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.