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.

Practice

1. What is the main purpose of using to_tsvector in Postgres full-text search?

easy

A. To convert text into a searchable document format

B. To insert new rows into a table

C. To delete unwanted text from a column

D. To create a backup of the database

Solution

1. Step 1: Understand to_tsvector function

   to_tsvector converts plain text into a special searchable format called a tsvector, which breaks text into words and normalizes them for searching.

2. Step 2: Identify its role in full-text search

   This conversion allows Postgres to quickly find matching words during a search query.

3. Final Answer:

   To convert text into a searchable document format -> Option A

4. Quick Check:

   to_tsvector = searchable format [OK]

Hint: Remember: to_tsvector prepares text for searching [OK]

Common Mistakes:

• Confusing to_tsvector with data insertion
• Thinking it deletes text
• Assuming it backs up data

2. Which of the following is the correct syntax to perform a full-text search using to_tsquery in Postgres?

easy

A. SELECT * FROM articles WHERE to_tsquery('search & term') @@ content;

B. SELECT * FROM articles WHERE to_tsquery(content) @@ 'search & term';

C. SELECT * FROM articles WHERE to_tsquery('search & term') = content;

D. SELECT * FROM articles WHERE content @@ to_tsquery('search & term');

Solution

1. Step 1: Understand to_tsquery usage

   to_tsquery creates a search query from a string like 'search & term'. It should be on the right side of the @@ operator.

2. Step 2: Check correct operator usage

   The @@ operator checks if the tsvector (usually a column) matches the tsquery. So the column (tsvector) is on the left, and to_tsquery is on the right.

3. Final Answer:

   SELECT * FROM articles WHERE content @@ to_tsquery('search & term'); -> Option D

4. Quick Check:

   tsvector @@ tsquery = correct syntax [OK]

Hint: Remember: tsvector column @@ to_tsquery('query') [OK]

Common Mistakes:

• Putting to_tsquery on the left side
• Using = instead of @@ operator
• Passing column name inside to_tsquery

3. Given the table products with a column description, what will this query return?

SELECT id FROM products WHERE to_tsvector(description) @@ to_tsquery('red & apple');

medium

A. All products with descriptions containing both 'red' and 'apple'

B. All products with descriptions containing either 'red' or 'apple'

C. All products with descriptions containing the exact phrase 'red apple'

D. No products because the syntax is incorrect

Solution

1. Step 1: Analyze the query logic

   The query uses to_tsvector(description) to convert descriptions into searchable format, then checks if it matches to_tsquery('red & apple').

2. Step 2: Understand the & operator in tsquery

   The & means AND, so it matches rows containing both 'red' and 'apple' anywhere in the description, not necessarily as a phrase.

3. Final Answer:

   All products with descriptions containing both 'red' and 'apple' -> Option A

4. Quick Check:

   'red & apple' means both words present [OK]

Hint: Use & for AND, | for OR in to_tsquery [OK]

Common Mistakes:

• Thinking & means OR
• Assuming exact phrase match
• Confusing syntax causing no results

4. You wrote this query but it returns no results:

SELECT * FROM feedback WHERE to_tsvector(comment) @@ to_tsquery('happy | sad');

What is the most likely reason?

medium

A. The to_tsquery syntax is invalid

B. The comment column is not indexed with a tsvector column

C. The @@ operator is used incorrectly

D. The query should use LIKE instead of full-text search

Solution

1. Step 1: Check query correctness

   The syntax and operator usage are correct for full-text search with to_tsvector and to_tsquery.

2. Step 2: Identify performance and result issues

   If no results appear, often the comment column is not indexed as a tsvector, so the search is slow or ineffective.

3. Final Answer:

   The comment column is not indexed with a tsvector column -> Option B

4. Quick Check:

   Missing tsvector index causes no results [OK]

Hint: Always create a tsvector index for fast full-text search [OK]

Common Mistakes:

• Assuming syntax error when query is correct
• Using LIKE instead of full-text search
• Misusing @@ operator

5. You want to build a search feature on a blog using Supabase and Postgres full-text search. Which approach best improves search speed and accuracy for large text columns?

hard

A. Store search keywords in a separate table and join on it

B. Use LIKE '%keyword%' queries on the text column without indexes

C. Create a generated tsvector column indexed with GIN and query using @@ operator

D. Use regular expressions to scan text columns at query time

Solution

1. Step 1: Understand full-text search optimization

   Creating a generated column that stores the tsvector form of the text allows fast searching without recalculating on each query.

2. Step 2: Use GIN index for fast lookup

   Adding a GIN index on the tsvector column speeds up searches dramatically, making queries efficient and scalable.

3. Step 3: Query with @@ operator for accuracy

   Using the @@ operator with to_tsquery on the indexed tsvector column ensures accurate full-text matching.

4. Final Answer:

   Create a generated tsvector column indexed with GIN and query using @@ operator -> Option C

5. Quick Check:

   Generated tsvector + GIN index = fast, accurate search [OK]

Hint: Index tsvector columns with GIN for best full-text search [OK]

Common Mistakes:

• Using LIKE instead of full-text search
• Not indexing tsvector columns
• Relying on regex for large text search