如何使用PostgreSQL ts_delete函数移除全文搜索向量元素？

Got it, let's walk through exactly how to use ts_delete to strip unwanted terms from your generated tsvector, including how to integrate this with your custom stopword table setup.

Basic Usage: Remove Single or Multiple Terms #

First, let's start with straightforward cases for removing specific terms manually. The ts_delete function takes two arguments: the original tsvector, and a tsquery representing the terms you want to remove.

Remove a Single Term #

If you want to remove, say, the term que from your vector:

SELECT ts_delete(
    strip(to_tsvector('simple', texto)),
    'que'::tsquery
) AS filtered_vector
FROM longtxts
WHERE id = 23;

Remove Multiple Terms #

To remove several terms at once (like em and que), combine them into a single tsquery using the | (OR) operator:

SELECT ts_delete(
    strip(to_tsvector('simple', texto)),
    'em | que'::tsquery
) AS filtered_vector
FROM longtxts
WHERE id = 23;

Running this will return a vector without the specified terms: 'alta' 'aluno' 'cada' 'do' 'leia' 'livro' 'pedir' 'trecho' 'um' 'voz'

Integrate with Your Custom Stopword Table #

Since you're storing custom stopwords in a database table, you can dynamically generate the tsquery from those terms instead of hardcoding them. Let's assume your stopword table is named custom_stopwords with a word column holding the terms to exclude.

Here's how to batch-delete all stopwords from your table:

WITH stopword_query AS (
    -- Aggregate stopwords into a single tsquery string (e.g., 'em | que | do')
    SELECT string_agg(word, ' | ')::tsquery AS exclude_terms
    FROM custom_stopwords
    -- Add a WHERE clause here if you need to filter stopwords for a specific user/context
)
SELECT ts_delete(
    strip(to_tsvector('simple', lt.texto)),
    sq.exclude_terms
) AS filtered_vector
FROM longtxts lt
CROSS JOIN stopword_query sq
WHERE lt.id = 23;

This query will automatically pull all stopwords from your table, convert them into a valid tsquery, and remove every matching term from your tsvector.

Key Notes #

• Matching Precision: Since you're using the simple dictionary, terms are matched exactly as they appear in the vector. If you switch to a language-specific dictionary (like portuguese, given your terms), make sure your stopwords match the stemmed form used in the vector.
• strip Function: The strip call cleans up any empty elements that might be left after deletion, keeping your vector tidy. It's optional but recommended for consistency.
• Performance: If you're running this on large datasets, consider precomputing the stopword tsquery or indexing your custom stopword table for faster aggregation.

内容的提问来源于stack exchange，提问作者user2129632