Postgres Full-Text

You are a PostgreSQL database developer. The user wants to implement full-text search using PostgreSQL's tsvector and tsquery functionality to efficiently search text columns.

What to check first

• Run SELECT * FROM pg_extension WHERE extname = 'pg_trgm'; to verify text search extensions are available
• Confirm PostgreSQL version with SELECT version(); — full-text search is stable in 9.6+

Steps

1. Create a table with a tsvector column to store preprocessed search data using ADD COLUMN content_ts tsvector
2. Populate the tsvector column with to_tsvector('english', column_name) to tokenize and normalize text
3. Create a GIN index on the tsvector column with CREATE INDEX idx_content_ts ON table_name USING GIN(content_ts)
4. Use @@ operator with to_tsquery('english', 'search_term') to perform fast full-text searches
5. Implement phrase matching by wrapping terms in single quotes: to_tsquery('english', '''exact phrase''')
6. Add OR/AND operators in queries using & (AND) and | (OR) in tsquery expressions
7. Rank results with ts_rank(content_ts, query) to sort by relevance score
8. Create a trigger to automatically update the tsvector column when the source text changes

Code

-- Create table with tsvector column
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    content_ts TSVECTOR
);

-- Create GIN index for fast full-text search
CREATE INDEX idx_articles_content_ts ON articles USING GIN(content_ts);

-- Create trigger to automatically populate tsvector on INSERT/UPDATE
CREATE OR REPLACE FUNCTION articles_update_ts() RETURNS TRIGGER AS $$
BEGIN
    NEW.content_ts := to_tsvector('english', COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.body, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER articles_ts_update BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION articles_update_ts();

-- Insert sample data
INSERT INTO articles (title, body) VALUES
    ('PostgreSQL Tips', 'Learn efficient full-text search techniques'),
    ('Database Optimization', 'Improve query performance with proper indexing'),
    ('SQL Fundamentals', 'Master the basics of structured query language');

-- Simple full-text search query
SELECT id, title, ts_rank(content_ts, query) AS rank
FROM articles, to_tsquery('english', 'database') AS query
WHERE content_ts @@ query
ORDER BY rank DESC;

-- Phrase search (exact phrase matching)
SELECT id, title, ts_rank(content_ts, query) AS rank
FROM articles, to_tsquery

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change