Full-text search with Postgres in Supabase - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

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

Supabase

CREATE INDEX idx_content_search ON documents USING gin(to_tsvector('english', [1]));

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

Atitle

Bcontent

Cauthor

Ddate

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column other than 'content' for the search index.

Forgetting to use the 'to_tsvector' function.

The to_tsvector function converts the 'content' column into a searchable text vector. Creating a GIN index on this vector speeds up full-text search queries.

2fill in blank

medium

Complete the code to perform a full-text search query for the word 'cloud'.

Supabase

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

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

Awebsearch_to_tsquery

Bto_tsquery

Cplainto_tsquery

Dts_rank

Attempts:

3 left

💡 Hint

Common Mistakes

Using to_tsquery which requires special syntax for complex queries.

Using ts_rank which is for ranking results, not querying.

plainto_tsquery converts a plain text query into a tsquery format suitable for matching. It is simple and effective for single words like 'cloud'.

3fill in blank

hard

Fix the error in the query to rank search results by relevance.

Supabase

SELECT *, [1](to_tsvector('english', content), plainto_tsquery('english', 'cloud')) AS rank FROM documents ORDER BY rank DESC;

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

Ato_tsquery

Bts_vector

Cplainto_tsquery

Dts_rank

Attempts:

3 left

💡 Hint

Common Mistakes

Using to_tsquery or plainto_tsquery which create queries but do not rank.

Using ts_vector which is not a function.

ts_rank calculates the relevance rank of each document based on the search query. This allows ordering results by how well they match.

4fill in blank

hard

Fill both blanks to create a materialized view that stores ranked search results for 'cloud'.

Supabase

CREATE MATERIALIZED VIEW cloud_search_results AS SELECT id, content, [1](to_tsvector('english', content), [2]('english', 'cloud')) AS rank FROM documents ORDER BY rank DESC;

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

Ats_rank

Bplainto_tsquery

Cto_tsquery

Dtsvector

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up to_tsquery and plainto_tsquery.

Using tsvector which is not a function.

The materialized view uses ts_rank to calculate relevance and plainto_tsquery to create the search query for 'cloud'. This stores ranked results for fast access.

5fill in blank

hard

Fill all three blanks to create a trigger that updates a tsvector column on insert or update.

Supabase

CREATE FUNCTION update_search_vector() RETURNS trigger AS $$ BEGIN NEW.search_vector := [1]('english', [2]); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON documents FOR EACH ROW EXECUTE FUNCTION [3]();

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

Ato_tsvector

Bcontent

Cupdate_search_vector

Dplainto_tsquery

Attempts:

3 left

💡 Hint

Common Mistakes

Using plainto_tsquery instead of to_tsvector.

Using the wrong column name.

Not matching the trigger function name.

The function uses to_tsvector on the 'content' column to update the 'search_vector' column. The trigger calls this function on insert or update to keep the search vector current.