GIN index for full-text search in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a GIN index on the column 'content' for full-text search.

PostgreSQL

CREATE INDEX idx_content_gin ON documents USING [1] (to_tsvector('english', content));

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

Abtree

Bhash

Cgin

Dgist

Attempts:

3 left

💡 Hint

Common Mistakes

Using btree index which is not suitable for full-text search.

Using hash index which does not support full-text search.

Using gist index which is less efficient for full-text search than gin.

The GIN index type is used for full-text search in PostgreSQL because it efficiently indexes composite values like tsvector.

2fill in blank

medium

Complete the code to convert the 'description' column to a tsvector for indexing.

PostgreSQL

SELECT to_tsvector([1], description) FROM products;

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

A'english'

B'simple'

C'french'

D'german'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'simple' which does not apply language-specific rules.

Using a language that does not match the text content.

The 'english' configuration is commonly used for English text to handle stemming and stop words.

3fill in blank

hard

Fix the error in the query to search for the word 'database' in the 'content' column using full-text search.

PostgreSQL

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

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

Atsvector

Bto_tsquery

Cwebsearch_to_tsquery

Dplainto_tsquery

Attempts:

3 left

💡 Hint

Common Mistakes

Using to_tsquery without proper syntax can cause errors.

Using tsvector which is not a query function.

plainto_tsquery converts plain text to a tsquery, suitable for simple search terms like 'database'.

4fill in blank

hard

Fill both blanks to create a GIN index on the 'text' column using the 'simple' dictionary.

PostgreSQL

CREATE INDEX idx_text_gin ON articles USING [1] (to_tsvector([2], text));

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

Agin

Bbtree

C'simple'

D'english'

Attempts:

3 left

💡 Hint

Common Mistakes

Using btree index which is not suitable for full-text search.

Using 'english' dictionary when 'simple' is required.

GIN is the correct index type and 'simple' is the dictionary used for tokenizing text without language rules.

5fill in blank

hard

Fill all three blanks to select rows where 'summary' matches the search term 'performance' using full-text search.

PostgreSQL

SELECT * FROM reports WHERE to_tsvector([1], summary) @@ [2]([3]);

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

A'english'

Bplainto_tsquery

C'performance'

D'simple'

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'simple' dictionary when English text is expected.

Using to_tsquery without proper syntax.

Not quoting the search term correctly.

The 'english' dictionary is used for parsing, plainto_tsquery converts the search term to a query, and 'performance' is the search word.