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.