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.

Practice

1. What is the main purpose of a search configuration in PostgreSQL?

easy

A. To define how text is processed for full-text search in a specific language

B. To store user login information securely

C. To manage database connections and pooling

D. To backup and restore database data

Solution

1. Step 1: Understand search configuration role

   Search configurations tell PostgreSQL how to handle text for searching, especially for different languages.

2. Step 2: Compare options

   Options A, C, and D relate to security, connections, and backups, not text processing.

3. Final Answer:

   To define how text is processed for full-text search in a specific language -> Option A

4. Quick Check:

   Search configuration = text processing [OK]

Hint: Search configs define text processing rules for languages [OK]

Common Mistakes:

• Confusing search configuration with user or connection settings
• Thinking it manages backups or security
• Assuming it stores data instead of processing text

2. Which of the following is the correct syntax to create a new text search configuration named my_config based on the built-in english configuration?

easy

A. CREATE TEXT SEARCH CONFIGURATION my_config AS english;

B. CREATE TEXT SEARCH CONFIGURATION my_config FROM english;

C. CREATE TEXT SEARCH CONFIGURATION my_config ( COPY = english );

D. CREATE TEXT SEARCH CONFIGURATION my_config LIKE english;

Solution

1. Step 1: Recall correct CREATE syntax

   PostgreSQL uses CREATE TEXT SEARCH CONFIGURATION name ( COPY = existing_config ) to copy an existing config.

2. Step 2: Check options

   Only CREATE TEXT SEARCH CONFIGURATION my_config ( COPY = english ); uses the correct syntax with COPY = english. Others use invalid keywords.

3. Final Answer:

   CREATE TEXT SEARCH CONFIGURATION my_config ( COPY = english ); -> Option C

4. Quick Check:

   Use COPY = for cloning configs [OK]

Hint: Use COPY = existing_config to clone search configs [OK]

Common Mistakes:

• Using FROM or AS instead of COPY =
• Omitting parentheses around COPY clause
• Confusing syntax with table creation

3. Given the following SQL commands:

CREATE TEXT SEARCH CONFIGURATION my_english ( COPY = english );
ALTER TEXT SEARCH CONFIGURATION my_english
  ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
  WITH simple;
SELECT to_tsvector('my_english', 'PostgreSQL is great!');

What is the output of the SELECT statement?

medium

A. 'postgresql':1 'great':3 'is':2 'simple':5

B. 'postgresql':1 'great':3 'is':2

C. 'postgresql':1 'great':3

D. Syntax error due to ALTER MAPPING

Solution

1. Step 1: Understand ALTER MAPPING effect

   The ALTER MAPPING changes how asciiword and related tokens are processed, using the simple dictionary which does not remove stop words.

2. Step 2: Analyze to_tsvector output

   The text 'PostgreSQL is great!' produces tokens: 'PostgreSQL' (pos 1, asciiword) -> 'postgresql':1; 'is' (pos 2, asciiword) -> 'is':2; 'great' (pos 3, asciiword) -> 'great':3. Simple indexes all without dropping stopwords. Matches 'postgresql':1 'great':3 'is':2.

3. Final Answer:

   'postgresql':1 'great':3 'is':2 -> Option B

4. Quick Check:

   Alter mapping changes dictionary used [OK]

Hint: ALTER MAPPING changes dictionary for word processing [OK]

Common Mistakes:

• Assuming stop words are always removed
• Confusing dictionary effects on stemming and stop words
• Expecting syntax error from ALTER MAPPING

4. You run the following command:

ALTER TEXT SEARCH CONFIGURATION my_config
  ALTER MAPPING FOR word WITH english_stem;

But you get an error: ERROR: dictionary "english_stem" does not exist
What is the most likely cause?

medium

A. The configuration name is reserved and cannot be changed

B. ALTER MAPPING cannot be used on existing configurations

C. You must drop the configuration before altering it

D. The dictionary name is misspelled or not created

Solution

1. Step 1: Analyze error message

   The error says the dictionary "english_stem" does not exist, indicating PostgreSQL cannot find that dictionary.

2. Step 2: Identify cause

   Most likely, the dictionary name is misspelled or the dictionary was never created in the database.

3. Final Answer:

   The dictionary name is misspelled or not created -> Option D

4. Quick Check:

   Dictionary must exist before use [OK]

Hint: Check dictionary exists before ALTER MAPPING [OK]

Common Mistakes:

• Assuming ALTER MAPPING is disallowed on configs
• Trying to drop config unnecessarily
• Ignoring dictionary creation step

5. You want to create a search configuration for French that uses the built-in french configuration but replaces the stopword dictionary with a custom dictionary named my_french_stop. Which sequence of commands correctly achieves this?

hard

A. CREATE TEXT SEARCH CONFIGURATION my_french ( COPY = french );
ALTER TEXT SEARCH CONFIGURATION my_french
ALTER MAPPING FOR stopword WITH my_french_stop;

B. CREATE TEXT SEARCH CONFIGURATION my_french ( COPY = french );
ALTER TEXT SEARCH CONFIGURATION my_french
ALTER MAPPING FOR word WITH my_french_stop;

C. CREATE TEXT SEARCH CONFIGURATION my_french AS french;
ALTER TEXT SEARCH CONFIGURATION my_french
ALTER MAPPING FOR stopword WITH my_french_stop;

D. CREATE TEXT SEARCH CONFIGURATION my_french ( COPY = french );
ALTER TEXT SEARCH CONFIGURATION my_french
ALTER MAPPING FOR stopword WITH french;

Solution

1. Step 1: Create configuration by copying french

   Use CREATE TEXT SEARCH CONFIGURATION my_french ( COPY = french ); to clone the built-in french config.

2. Step 2: Alter mapping for stopword dictionary

   Use ALTER MAPPING FOR stopword WITH my_french_stop; to replace the stopword dictionary with the custom one.

3. Final Answer:

   CREATE TEXT SEARCH CONFIGURATION my_french ( COPY = french ); ALTER TEXT SEARCH CONFIGURATION my_french ALTER MAPPING FOR stopword WITH my_french_stop; -> Option A

4. Quick Check:

   Copy config then alter stopword mapping [OK]

Hint: Copy config then ALTER MAPPING for custom dictionaries [OK]

Common Mistakes:

• Using AS instead of COPY = for creation
• Altering wrong token types like word instead of stopword
• Assigning built-in dictionary instead of custom one