PostgreSQL 11 动态排除客户表中指定域名邮箱的类型报错求解

原代码报错的核心原因 #

• 你通过CTE拼接生成的con字段本质是纯文本字符串，PostgreSQL的SQL解析器只会在语句预编译阶段处理静态定义的布尔判断逻辑，不会在运行时把返回的文本内容自动解析为可执行的WHERE过滤条件，直接将文本填入WHERE子句，自然会触发“WHERE参数必须为布尔类型”的类型不匹配报错。
• 原有拼接逻辑存在三个隐蔽缺陷：一是email ILIKE '%gmail.com%'是全字段模糊匹配，会误匹配任意位置包含该字符串的异常值（比如gmail.com@other.com、testgmail.com@xxx.com这类无效邮箱），匹配范围完全失准；二是拼接完成的字符串末尾会多出一个悬空的OR关键字，就算强行做动态执行，也会触发语法错误；三是拼接的是“匹配指定域名”的正向逻辑，直接放入WHERE子句会把需要排除的记录筛选出来，和你要排除对应记录的需求完全相反。

推荐的高效静态实现方案 #

完全不需要动态拼接SQL文本，通过关联域名列表的原生布尔判断就能实现需求，同时兼顾准确性和性能：

WITH excluded_domains AS (
    -- 在这里维护所有需要排除的域名列表即可，后续增减不需要修改查询核心逻辑
    SELECT 'gmail.com' AS dom
    UNION ALL
    SELECT 'yahoo.com'
)
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM excluded_domains ed
    -- 精准提取邮箱@符号后的域名部分，做大小写不敏感比对，避免子串误匹配
    WHERE lower(split_part(c.email, '@', 2)) = lower(ed.dom)
);

这个方案的优势：

• 不存在类型匹配问题，所有过滤逻辑都是原生SQL支持的布尔表达式，可直接正常执行
• 匹配逻辑精准：仅对邮箱地址结构中@后的域名部分做比对，不会出现模糊匹配的误判问题
• 性能可调：如果数据量较大，只需要建一个表达式索引就能大幅提速，示例：
  CREATE INDEX idx_customer_email_domain ON customers (lower(split_part(email, '@', 2)));
  建索引后，NOT EXISTS的关联匹配可以直接走索引扫描，效率远高于全字段ILIKE的顺序扫描
• 可维护性强：调整排除的域名范围时，只需要在excluded_domains的CTE里增删记录行即可，不需要改动核心查询逻辑

关于动态拼接思路的正确实现 #

如果你确实有动态生成过滤条件的场景（比如域名列表来自外部输入，无法写死在静态查询里），在PostgreSQL中不能直接把拼接的文本塞到普通查询的WHERE子句里，必须通过PL/pgSQL的动态SQL执行机制把文本解析为可执行逻辑，参考写法：

DO $$
DECLARE
    v_filter_cond text;
BEGIN
    -- 先把域名列表拼接为合法的布尔表达式，去掉多余的逻辑连接符
    SELECT string_agg(
        format('lower(split_part(email, ''@'', 2)) = lower(%L)', dom),
        ' OR '
    ) INTO v_filter_cond
    FROM (VALUES ('gmail.com'), ('yahoo.com')) AS t(dom);

    -- 通过EXECUTE把拼接的文本真正解析为执行计划，完成排除逻辑
    EXECUTE format(
        'CREATE TEMP TABLE IF NOT EXISTS tmp_valid_customers AS 
         SELECT * FROM customers WHERE NOT (%s)',
        v_filter_cond
    );
END $$;

-- 后续直接查询临时表即可拿到过滤后的客户数据
SELECT * FROM tmp_valid_customers;

需要注意的是，这种动态SQL写法仅适合规则复杂的动态查询场景：

• 域名如果来自外部用户输入，必须做参数转义，否则会有SQL注入风险
• 数据库优化器无法提前预判动态生成的查询逻辑，执行计划的稳定性不如静态SQL
• 对于固定域名列表的简单排除需求，完全不需要用这种方案，前面的NOT EXISTS写法是最优选择

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