Full-text search with Postgres in Supabase - Mini Project: Build & Apply

Full-text search with Postgres

📖 Scenario: You are building a simple blog platform using Supabase, which uses Postgres as its database. You want to add a feature that allows users to search blog posts by keywords in the title and content.

🎯 Goal: Create a Postgres table for blog posts and implement a full-text search configuration that allows searching posts by keywords in the title and content.

📋 What You'll Learn

Create a table called posts with columns id, title, and content.

Add a column search_vector of type tsvector to store searchable text.

Create a trigger to update search_vector automatically when title or content changes.

Write a query that uses full-text search to find posts matching a search phrase.

💡 Why This Matters

🌍 Real World

Full-text search is used in many applications like blogs, e-commerce, and document management to let users find relevant content quickly.

💼 Career

Knowing how to implement full-text search with Postgres is valuable for backend developers and cloud engineers working with databases and search features.

1

Create the posts table

Create a table called posts with columns id as serial primary key, title as text, and content as text.

Supabase

-- Your code here

Hint

Use SERIAL PRIMARY KEY for id to auto-increment.

2

Add a search_vector column

Alter the posts table to add a column called search_vector of type tsvector.

Supabase

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT
);
-- Your code here

Hint

The tsvector type stores searchable text for full-text search.

3

Create a trigger to update search_vector

Create a trigger function called posts_search_vector_update that updates search_vector by combining title and content using to_tsvector('english', ...). Then create a trigger on posts that calls this function before insert or update.

Supabase

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT
);

ALTER TABLE posts ADD COLUMN search_vector tsvector;
-- Your code here

Hint

The trigger updates search_vector automatically when a post is added or changed.

4

Write a full-text search query

Write a SELECT query to find all columns from posts where search_vector matches the search phrase 'cloud infrastructure' using to_tsquery('english', 'cloud & infrastructure'). Order results by rank using ts_rank.

Supabase

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT
);

ALTER TABLE posts ADD COLUMN search_vector tsvector;

CREATE FUNCTION posts_search_vector_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', coalesce(NEW.title, '') || ' ' || coalesce(NEW.content, ''));
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION posts_search_vector_update();

-- Your code here

Hint

Use @@ operator to match search_vector with to_tsquery. Use ts_rank to order results by relevance.