Why full-text search matters in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all rows where the text column matches the search query using full-text search.

PostgreSQL

SELECT * FROM articles WHERE to_tsvector('english', content) @@ [1];

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

Aplainto_tsquery('english', 'database')

Bto_tsquery('database')

Cplainto_tsquery('database')

Dto_tsquery('english', 'database')

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to specify the language in the function.

Using to_tsquery without language parameter incorrectly.

The plainto_tsquery function converts plain text to a tsquery for matching. It is used here with the language specified to match the content.

2fill in blank

medium

Complete the code to create a full-text search index on the 'content' column of the 'articles' table.

PostgreSQL

CREATE INDEX idx_content_search ON articles USING gin([1]);

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

Ato_tsvector('english', content)

Bto_tsquery('english', content)

Cplainto_tsquery('english', content)

Dtsvector('english', content)

Attempts:

3 left

💡 Hint

Common Mistakes

Using to_tsquery instead of to_tsvector in the index.

Not specifying the language in to_tsvector.

The to_tsvector function converts the text column into a tsvector type, which is needed for creating a GIN index for full-text search.

3fill in blank

hard

Fix the error in the query to search for articles containing the phrase 'full text search'.

PostgreSQL

SELECT * FROM articles WHERE to_tsvector('english', content) @@ [1];

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

Ato_tsquery('full & text & search')

Bto_tsquery('english', 'full & text & search')

Cplainto_tsquery('english', 'full text search')

Dto_tsquery('english', 'full text search')

Attempts:

3 left

💡 Hint

Common Mistakes

Passing phrase without operators to to_tsquery.

Omitting language parameter in to_tsquery.

The to_tsquery function requires operators like & to combine words for phrase search. The correct syntax uses 'full & text & search' with language specified.

4fill in blank

hard

Fill both blanks to create a query that ranks articles by relevance to the search term 'database'.

PostgreSQL

SELECT title, ts_rank([1], [2]) AS rank FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database') ORDER BY rank DESC;

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

Ato_tsvector('english', content)

Bto_tsquery('english', 'database')

Cplainto_tsquery('english', 'database')

Dto_tsvector(content)

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the order of arguments in ts_rank.

Using plainto_tsquery instead of to_tsquery for ranking.

The ts_rank function takes a tsvector and a tsquery to calculate relevance. The tsvector is created from the content column, and the tsquery is the search term.

5fill in blank

hard

Fill all three blanks to create a materialized view that stores articles with their search vectors for faster full-text search.

PostgreSQL

CREATE MATERIALIZED VIEW article_search AS SELECT id, title, content, [1] AS document_vector, [2] AS query_vector FROM articles, to_tsquery('english', [3]) query WHERE to_tsvector('english', content) @@ query;

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

Ato_tsvector('english', content)

B'database'

Cplainto_tsquery('english', 'database')

Dto_tsquery('english', 'database')

Attempts:

3 left

💡 Hint

Common Mistakes

Using plainto_tsquery instead of to_tsquery for the query vector.

Not passing the search term as a string literal.

The materialized view stores the tsvector of content as document_vector. The query vector is created with to_tsquery using the search term 'database'. The search term is passed as a string to to_tsquery.

Practice

1. Why is full-text search important in PostgreSQL?

easy

A. It automatically corrects spelling mistakes in queries.

B. It allows fast searching of words inside large text columns.

C. It stores data in encrypted form for security.

D. It creates backups of the database automatically.

Solution

1. Step 1: Understand full-text search purpose

   Full-text search is designed to quickly find words or phrases inside large text fields.

2. Step 2: Compare options to purpose

   Only It allows fast searching of words inside large text columns. describes this purpose correctly; others describe unrelated features.

3. Final Answer:

   It allows fast searching of words inside large text columns. -> Option B

4. Quick Check:

   Full-text search = fast word search [OK]

Hint: Full-text search = fast word search in big text [OK]

Common Mistakes:

• Confusing full-text search with spell check
• Thinking it encrypts data
• Assuming it handles backups

2. Which of the following is the correct way to convert a text column for full-text search in PostgreSQL?

easy

A. SELECT to_tsvector('english', column_name) FROM table_name;

B. SELECT to_tsquery('english', column_name) FROM table_name;

C. SELECT to_tsvector(column_name) FROM table_name;

D. SELECT to_tsquery(column_name) FROM table_name;

Solution

1. Step 1: Identify function for text vector conversion

   to_tsvector converts text into a searchable document vector; it requires a configuration like 'english'.

2. Step 2: Check syntax correctness

   SELECT to_tsvector('english', column_name) FROM table_name; uses to_tsvector with language and column, which is correct syntax.

3. Final Answer:

   SELECT to_tsvector('english', column_name) FROM table_name; -> Option A

4. Quick Check:

   to_tsvector needs language + text [OK]

Hint: Use to_tsvector('language', text) to prepare text [OK]

Common Mistakes:

• Using to_tsquery instead of to_tsvector for conversion
• Omitting language configuration
• Passing wrong argument order

3. Given the table articles with a column content, what will this query return?

SELECT id FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & search');

medium

A. Syntax error due to incorrect query.

B. All article IDs where content contains either 'database' or 'search'.

C. All article IDs where content contains the exact phrase 'database search'.

D. All article IDs where content contains both 'database' and 'search'.

Solution

1. Step 1: Understand the @@ operator

   The @@ operator checks if the text vector matches the text query.

2. Step 2: Interpret the query string 'database & search'

   The '&' means AND, so both words must be present in the content.

3. Final Answer:

   All article IDs where content contains both 'database' and 'search'. -> Option D

4. Quick Check:

   '&' means AND in to_tsquery [OK]

Hint: Use '&' in to_tsquery for AND condition [OK]

Common Mistakes:

• Thinking '&' means OR
• Assuming phrase match instead of word match
• Believing query causes syntax error

4. You wrote this query but it returns no rows:

SELECT * FROM emails WHERE to_tsvector('english', body) @@ to_tsquery('english', 'urgent | meeting');

What is the most likely reason?

medium

A. The body column is NULL in all rows.

B. The to_tsquery syntax is incorrect; '|' is not valid.

C. The query searches for 'urgent' OR 'meeting', but none exist in body.

D. to_tsvector cannot be used on text columns.

Solution

1. Step 1: Understand the query logic

   The '|' operator means OR, so it matches rows containing 'urgent' or 'meeting'.

2. Step 2: Analyze why no rows return

   If no rows match, likely none contain these words in the body column.

3. Final Answer:

   The query searches for 'urgent' OR 'meeting', but none exist in body. -> Option C

4. Quick Check:

   '|' means OR; no matches means words missing [OK]

Hint: Check if searched words exist in text before blaming syntax [OK]

Common Mistakes:

• Thinking '|' is invalid syntax
• Assuming to_tsvector can't be used on text
• Ignoring possibility of no matching data

5. You want to find articles that mention either 'database' or 'search' but NOT 'slow'. Which query correctly does this using full-text search?

hard

A. SELECT id FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', '(database | search) & !slow');

B. SELECT id FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database | (search & !slow)');

C. SELECT id FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database | search & !slow');

D. SELECT id FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & search & !slow');

Solution

1. Step 1: Understand operator precedence in to_tsquery

   AND (&) has higher precedence than OR (|), so parentheses are needed to group OR terms.

2. Step 2: Analyze each option

   SELECT id FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', '(database | search) & !slow'); correctly groups 'database | search' together, then AND NOT 'slow'. Others either miss grouping or change logic.

3. Final Answer:

   SELECT id FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', '(database | search) & !slow'); -> Option A

4. Quick Check:

   Use parentheses to group OR before AND NOT [OK]

Hint: Group OR terms with parentheses before AND/NOT [OK]

Common Mistakes:

• Missing parentheses causing wrong logic
• Using AND instead of OR for 'database' or 'search'
• Placing NOT operator incorrectly