PostgreSQL关键词与商标匹配报错修复及字段更新实现问询

我有两个PostgreSQL表用于存储词汇和注册商标，表结构及初始化数据如下：

CREATE TABLE IF NOT EXISTS words
(
    id bigint NOT NULL DEFAULT nextval('processed_words_id_seq'::regclass),
    keyword character varying(300) COLLATE pg_catalog."default",
    trademark_blacklisted character varying(300) COLLATE pg_catalog."default"
);

INSERT INTO words (keyword, trademark_blacklisted)
VALUES ('while swam is interesting', 'ibm is a company');

CREATE TABLE IF NOT EXISTS trademarks
(
   id bigint NOT NULL DEFAULT nextval('trademarks_id_seq'::regclass),
   trademark character varying(300) COLLATE pg_catalog."default"
);

INSERT INTO trademarks (trademark)
VALUES ('swam'), ('ibm');

注：原初始化语句存在错误，已修正INSERT INTO trademarks的写法

trademarks表将存储数千个注册商标名称，我需要实现以下需求：

• 检测words表的keyword字段内容是否匹配trademarks中的商标，支持匹配短语中的单个词（例如words.keyword中的"while swam is interesting"需匹配到trademarks.trademark中的"swam"）
• 将匹配到的商标更新到words.trademark_blacklisted字段中
• 支持按参数查询单个词是否属于黑名单商标

我尝试了以下SQL语句，但出现报错：

SELECT w.id, w.keyword, t.trademark 
FROM words w
INNER JOIN trademarks t ON w.keyword::tsvector @@ 
regexp_replace(t.trademark, '\s', ' | ', 'g' )::tsquery;

报错信息：

ERROR:  no operand in tsquery: "Google | "
SQL state: 42601

1. 修复报错并实现正确匹配逻辑 #

报错原因是trademark字段可能存在首尾空格、空值，或处理后生成了无效的tsquery（比如末尾带|）。以下是两种可行的实现方式：

方法一：全文搜索（推荐，性能更优） #

通过plainto_tsquery自动处理商标内容，生成合法查询规则，同时过滤无效数据：

SELECT 
    w.id, 
    w.keyword, 
    string_agg(DISTINCT t.trademark, ', ') AS matched_trademarks
FROM words w
JOIN trademarks t 
    ON w.keyword::tsvector @@ plainto_tsquery(t.trademark)
    AND t.trademark IS NOT NULL 
    AND trim(t.trademark) != ''
GROUP BY w.id, w.keyword;

• plainto_tsquery会将多词短语转换为合法的逻辑查询（比如"Google LLC"转为'google' & 'llc'，匹配同时包含这两个词的内容）
• 若需匹配短语中任意单个词，可替换为to_tsquery(regexp_replace(trim(t.trademark), '\s+', ' | ', 'g'))，确保trademark不为空且无首尾空格

方法二：LIKE匹配（简单直观，适合小数据量） #

通过前后补空格避免部分匹配（比如防止"swam"匹配"swamp"）：

SELECT 
    w.id, 
    w.keyword, 
    string_agg(DISTINCT t.trademark, ', ') AS matched_trademarks
FROM words w
JOIN trademarks t 
    ON ' ' || w.keyword || ' ' LIKE '% ' || trim(t.trademark) || ' %'
    AND t.trademark IS NOT NULL 
    AND trim(t.trademark) != ''
GROUP BY w.id, w.keyword;

2. 更新words.trademark_blacklisted字段 #

将匹配到的商标合并到目标字段，支持追加去重：

UPDATE words w
SET trademark_blacklisted = COALESCE(w.trademark_blacklisted || ', ', '') || sub.matched_trademarks
FROM (
    SELECT 
        w.id, 
        string_agg(DISTINCT t.trademark, ', ') AS matched_trademarks
    FROM words w
    JOIN trademarks t 
        ON w.keyword::tsvector @@ plainto_tsquery(t.trademark)
        AND t.trademark IS NOT NULL 
        AND trim(t.trademark) != ''
    GROUP BY w.id
) sub
WHERE w.id = sub.id;

若需覆盖原有字段值，直接删除COALESCE(w.trademark_blacklisted || ', ', '') ||即可。

3. 查询单个词是否属于黑名单商标 #

通过简单的SELECT语句即可实现，支持参数化查询：

-- 静态查询
SELECT EXISTS(
    SELECT 1 FROM trademarks 
    WHERE trim(trademark) = 'swam'
);

-- 参数化查询（PostgreSQL语法）
PREPARE check_trademark(text) AS
SELECT EXISTS(
    SELECT 1 FROM trademarks 
    WHERE trim(trademark) = $1
);

-- 执行参数化查询
EXECUTE check_trademark('ibm');

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