Ranking with ts_rank in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the rank of the text search vector using ts_rank.

PostgreSQL

SELECT ts_rank([1], to_tsquery('english', 'database')) AS rank FROM documents;

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

Adocument_vector

Bto_tsvector('english', content)

Ccontent

Dto_tsquery('english', content)

Attempts:

3 left

💡 Hint

Common Mistakes

Passing plain text instead of a tsvector to ts_rank.

Using to_tsquery as the first argument instead of to_tsvector.

The ts_rank function requires a tsvector as its first argument. Using to_tsvector('english', content) converts the text content into a tsvector suitable for ranking.

2fill in blank

medium

Complete the code to filter documents matching the search query using @@ operator.

PostgreSQL

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

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

A=

BIN

CLIKE

D@@

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of '@@' for text search matching.

Using LIKE which is for string pattern matching, not full-text search.

The @@ operator checks if the tsvector matches the tsquery, filtering relevant documents.

3fill in blank

hard

Fix the error in the code to correctly rank and order documents by relevance.

PostgreSQL

SELECT content, ts_rank(to_tsvector('english', content), to_tsquery('english', 'database')) AS rank FROM documents ORDER BY [1] DESC;

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

Arank

Bcontent

Cts_rank

Dto_tsvector

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by the content column instead of the rank.

Using the function name ts_rank directly in ORDER BY without alias.

The alias rank is used in the ORDER BY clause to sort results by their relevance score.

4fill in blank

hard

Fill both blanks to create a query that selects content and ranks it, filtering only relevant documents.

PostgreSQL

SELECT content, ts_rank([1], [2]) AS rank FROM documents WHERE [1] @@ [2] ORDER BY rank DESC;

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

Ato_tsvector('english', content)

Bto_tsquery('english', 'database')

Ccontent

Dts_rank

Attempts:

3 left

💡 Hint

Common Mistakes

Using plain content instead of to_tsvector for ranking and filtering.

Mixing up the order of arguments in ts_rank or @@ operator.

The to_tsvector converts content to a searchable vector, and to_tsquery creates the search query. Both are used for ranking and filtering.

5fill in blank

hard

Fill all three blanks to create a query that ranks documents with weighted ts_rank and filters by search query.

PostgreSQL

SELECT content, ts_rank([3], [1], [2]) AS rank FROM documents WHERE [1] @@ [2] ORDER BY rank DESC;

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

Ato_tsvector('english', content)

Bto_tsquery('english', 'database')

C'ABCD'

D'english'

Attempts:

3 left

💡 Hint

Common Mistakes

Passing plain text or language name as the third argument instead of weights string.

Using different expressions for ranking and filtering.

The weights string is the first argument to ts_rank, followed by the tsvector and the tsquery. This prioritizes matches based on position (A=0.1, B=0.2, C=0.4, D=1.0).