Search configuration and languages in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a full-text search with language configuration

Given a table documents with a content column, what is the output of this query?

SELECT content FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('english', 'cat & dog');

Assume the table has these rows:
1. 'The cat chased the dog.'
2. 'A dog and a cat are friends.'
3. 'Cats are independent animals.'

PostgreSQL

CREATE TABLE documents (content TEXT);
INSERT INTO documents VALUES
('The cat chased the dog.'),
('A dog and a cat are friends.'),
('Cats are independent animals.');

SELECT content FROM documents WHERE to_tsvector('english', content) @@ to_tsquery('english', 'cat & dog');

A No rows returned

B 'Cats are independent animals.'

C

'The cat chased the dog.'
'A dog and a cat are friends.'
'Cats are independent animals.'

D

'The cat chased the dog.'
'A dog and a cat are friends.'

💡 Hint

Remember that the query looks for rows containing both 'cat' and 'dog' after stemming and stop-word removal.

The query uses the English text search configuration. It looks for rows where both 'cat' and 'dog' appear. The first two rows contain both words, so they match. The third row only contains 'cats' (plural), which is stemmed to 'cat', but it does not contain 'dog', so it does not match.

🧠 Conceptual

intermediate

Purpose of search configuration languages in PostgreSQL

What is the main purpose of specifying a language in PostgreSQL's full-text search configuration?

A To change the database encoding to match the language

B To apply language-specific rules for stemming and stop-word removal

C To translate the search query into multiple languages automatically

D To enable case-sensitive search only for that language

💡 Hint

Think about how words are reduced to their root forms and common words are ignored.

Specifying a language in full-text search tells PostgreSQL which stemming rules and stop-word lists to use. This helps match different forms of words and ignore common words that do not add meaning.

📝 Syntax

advanced

Correct syntax to create a custom text search configuration

Which of the following commands correctly creates a new text search configuration named my_english based on the built-in english configuration?

A CREATE TEXT SEARCH CONFIGURATION my_english ( COPY = english );

B CREATE TEXT SEARCH CONFIGURATION my_english AS english;

C CREATE TEXT SEARCH CONFIGURATION my_english FROM english;

D CREATE TEXT SEARCH CONFIGURATION my_english LIKE english;

💡 Hint

The syntax uses COPY to duplicate an existing configuration.

The correct syntax to create a new configuration based on an existing one is to use COPY with parentheses. Other options are invalid syntax.

🔧 Debug

advanced

Why does this full-text search query return no results?

Consider this query:

SELECT * FROM articles WHERE to_tsvector('simple', body) @@ to_tsquery('english', 'databases');

Given that body contains the word 'databases' in some rows, why might this query return no rows?

A Because the <code>to_tsvector</code> and <code>to_tsquery</code> use different language configurations

B Because the <code>body</code> column is not indexed

C Because <code>to_tsquery</code> requires the query string to be in uppercase

D Because the word 'databases' is a stop word in the 'simple' configuration

💡 Hint

Check if the text search configurations match in both functions.

The to_tsvector uses the 'simple' configuration, which does no stemming or stop-word removal, while to_tsquery uses 'english', which applies stemming and stop-word removal. This mismatch causes the query to fail to match.

❓ optimization

expert

Optimizing full-text search for multiple languages

You have a multilingual table with a content column and a language column specifying the language of each row (e.g., 'english', 'french'). You want to create a full-text search index that respects each row's language for stemming and stop-word removal.

Which approach is the best to optimize search performance?

A Create separate tsvector columns for each language and create separate indexes, then query the correct one based on language

B Create no index and rely on sequential scans with <code>to_tsvector(language, content)</code> in the WHERE clause

C Create a single tsvector column using <code>to_tsvector(language, content)</code> and create a GIN index on it

D Create a tsvector column using a fixed language configuration (e.g., 'english') for all rows and ignore the language column

💡 Hint

Think about how to store and index the tsvector efficiently for multiple languages.

Using to_tsvector(language, content) stores the language-specific processed text in one column, allowing a single GIN index to be used efficiently for all rows regardless of language. Separate columns and indexes increase complexity and storage.