Full-text search with Postgres in Supabase

Introduction

Full-text search helps you find words or phrases quickly in large text data. It works like a smart search engine inside your database.

You want users to search blog posts by keywords.

You need to find products by description in an online store.

You want to filter customer feedback by certain phrases.

You want to build a search box that finds relevant text fast.

Syntax

Supabase

SELECT * FROM table WHERE to_tsvector('english', column) @@ to_tsquery('english', 'search_query');

to_tsvector converts text into searchable tokens.

to_tsquery creates the search query from your keywords.

Examples

Searches for rows in articles where content contains both 'cloud' and 'infrastructure'.

Supabase

SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'cloud & infrastructure');

Finds products with descriptions containing either 'fast' or 'quick'.

Supabase

SELECT * FROM products WHERE to_tsvector('english', description) @@ to_tsquery('english', 'fast | quick');

Finds feedback comments with words starting with 'error' like 'errors' or 'erroring'.

Supabase

SELECT * FROM feedback WHERE to_tsvector('english', comments) @@ to_tsquery('english', 'error:*');

Sample Program

This creates a simple articles table, adds three rows, and searches for articles whose content contains both 'cloud' and 'servers'.

Supabase

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

INSERT INTO articles (title, content) VALUES
('Cloud Basics', 'Cloud computing is a way to use servers over the internet.'),
('Infrastructure Guide', 'Infrastructure includes hardware and software components.'),
('Fast Search', 'Full-text search helps find words quickly in text data.');

SELECT id, title FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'cloud & servers');

Important Notes

Full-text search ignores common words like 'the' or 'is' to speed up search.

Use indexes on to_tsvector columns for faster search on large data.

Supabase supports Postgres full-text search natively, so you can run these queries directly.

Summary

Full-text search finds words or phrases inside text columns quickly.

Use to_tsvector and to_tsquery functions in Postgres to build search queries.

It works well for blogs, products, feedback, or any text-heavy data.