PostgreSQL Pro | Database Mastery

📌 Tutorial: Full-Text Search in PostgreSQL – Powering Advanced Search

🔹 Introduction:
If you need to search large text fields (like descriptions or documents), full-text search in PostgreSQL is an efficient way to implement advanced search features directly in your database. This functionality enables quick searching within text, ideal for applications like document management, e-commerce, and more!

1️⃣ Setting Up Full-Text Search:

PostgreSQL provides the tsvector and tsquery data types for storing and querying searchable text.

- tsvector: Stores searchable text in a preprocessed form.
- tsquery: Represents search queries that can be matched against tsvector data.

Example:

CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    content_tsvector TSVECTOR
);

Here, content_tsvector will store the processed version of content for fast searching.

2️⃣ Populating tsvector with Text Data:

The to_tsvector function converts text to tsvector, making it searchable.

Example:

UPDATE articles
SET content_tsvector = to_tsvector('english', content);

This command processes content in English, creating a searchable tsvector.

3️⃣ Basic Text Search:

To perform a search, use to_tsquery, which converts the search term into a tsquery.

Example:

SELECT title
FROM articles
WHERE content_tsvector @@ to_tsquery('english', 'database');

This query searches for "database" within the content_tsvector field.

4️⃣ Adding Triggers for Automatic Updates:

To keep content_tsvector up-to-date with content, set up a trigger.

CREATE FUNCTION update_tsvector() RETURNS TRIGGER AS $$
BEGIN
    NEW.content_tsvector := to_tsvector('english', NEW.content);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

This trigger automatically updates content_tsvector whenever content changes.

5️⃣ Phrase Searches with plainto_tsquery:

If you want a search term treated as a single phrase, plainto_tsquery converts it into a tsquery.

Example:

SELECT title
FROM articles
WHERE content_tsvector @@ plainto_tsquery('english', 'full text search');

This query searches for the exact phrase "full text search."

6️⃣ Highlighting Search Results:

The ts_headline function highlights search terms in results.

Example:

SELECT title, ts_headline('english', content, to_tsquery('full & text & search')) AS preview
FROM articles
WHERE content_tsvector @@ to_tsquery('english', 'full & text & search');

This highlights occurrences of "full text search" in the content preview.

7️⃣ Ranking Results by Relevance:

Use ts_rank or ts_rank_cd to rank results based on how well they match the query.

Example:

SELECT title, ts_rank(content_tsvector, to_tsquery('database')) AS rank
FROM articles
WHERE content_tsvector @@ to_tsquery('database')
ORDER BY rank DESC;

This ranks articles based on their relevance to the term "database."

8️⃣ Combining Full-Text Search with SQL Filters:

You can combine full-text search with other SQL conditions for more refined queries.

Example:

SELECT title
FROM articles
WHERE content_tsvector @@ to_tsquery('database')
AND publication_date > '2023-01-01';

This searches for articles containing "database" published after January 1, 2023.

9️⃣ Indexing for Full-Text Search:

For faster full-text search, create a GIN index on the tsvector column.

Example:

CREATE INDEX idx_content_tsvector ON articles USING gin(content_tsvector);

This index improves search performance, especially with large datasets.

@postgres

👍1

167 views07:21