tsvector and tsquery types in PostgreSQL

Introduction

These types help you search text quickly and smartly in a database. They let you find words or phrases inside big text data easily.

You want to find all articles containing certain keywords fast.

You need to search a product catalog by description or features.

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

You want to filter emails or messages by specific words.

You want to rank search results by how well they match your query.

Syntax

PostgreSQL

tsvector: stores processed text for searching

tsquery: stores search conditions to match against tsvector

tsvector breaks text into words and stores them in a searchable form.

tsquery defines what words or phrases you want to find.

Examples

This converts a sentence into a tsvector for searching.

PostgreSQL

SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');

This creates a tsquery to find rows containing both 'fox' and 'dog'.

PostgreSQL

SELECT to_tsquery('english', 'fox & dog');

This checks if the text contains both 'powerful' and 'database'. It returns true or false.

PostgreSQL

SELECT to_tsvector('english', 'PostgreSQL is a powerful database') @@ to_tsquery('english', 'powerful & database');

Sample Program

This creates a table with text, inserts some rows, and searches for rows containing both 'powerful' and 'database'.

PostgreSQL

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

INSERT INTO articles (content) VALUES
('The quick brown fox jumps over the lazy dog'),
('PostgreSQL is a powerful open-source database'),
('Full text search is very useful');

SELECT id, content
FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'powerful & database');

Important Notes

Use to_tsvector to prepare text for searching.

Use to_tsquery to build search queries with AND (&), OR (|), and NOT (!) operators.

The @@ operator checks if the text matches the search query.

Summary

tsvector stores searchable text.

tsquery defines what to search for.

Use @@ to test if text matches your search.