Why full-text search matters in PostgreSQL

Introduction

Full-text search helps you quickly find words or phrases inside large amounts of text. It makes searching easier and faster than looking word by word.

You want to find all articles containing a specific word or phrase.

You need to search customer reviews for certain keywords.

You want to build a search box that finds relevant documents quickly.

You want to filter emails or messages by content.

You want to analyze text data to find trends or important topics.

Syntax

PostgreSQL

SELECT * FROM table WHERE to_tsvector(column) @@ to_tsquery('search_term');

Use to_tsvector to convert text into searchable form.

Use to_tsquery to create the search query.

Examples

Finds all articles where the word 'database' appears in the content.

PostgreSQL

SELECT * FROM articles WHERE to_tsvector(content) @@ to_tsquery('database');

Finds reviews containing both 'fast' and 'reliable'.

PostgreSQL

SELECT * FROM reviews WHERE to_tsvector(comment) @@ to_tsquery('fast & reliable');

Finds emails containing either 'urgent' or 'important'.

PostgreSQL

SELECT * FROM emails WHERE to_tsvector(body) @@ to_tsquery('urgent | important');

Sample Program

This example creates a books table, adds some books, and searches for books whose description contains both 'SQL' and 'speed'.

PostgreSQL

CREATE TABLE books (id SERIAL PRIMARY KEY, title TEXT, description TEXT);

INSERT INTO books (title, description) VALUES
('Learn SQL', 'A book about learning SQL databases'),
('Cooking Tips', 'Tips and tricks for cooking delicious meals'),
('SQL Performance', 'How to optimize SQL queries for speed');

SELECT title FROM books WHERE to_tsvector(description) @@ to_tsquery('SQL & speed');

Important Notes

Full-text search is faster than using LIKE for big text data.

It ignores common words like 'the' or 'and' to focus on important words.

You can combine words with AND (&), OR (|), and NOT (!) in to_tsquery.

Summary

Full-text search helps find words inside large text quickly.

Use to_tsvector and to_tsquery to search text columns.

It works well for searching articles, reviews, emails, and more.