PostgreSQL中~~ any()搭配聚合数组执行删除无效果问题咨询

你遇到的不生效问题核心是两个错误：

• 类型不匹配：手动写的'{%auchan%,%abc%}'是SQL可自动识别的数组字面量，会被解析为text[]数组类型；但你子查询中拼接大括号生成的是普通text字符串，ANY运算符不会自动将字符串解析为数组，导致所有匹配判断都返回false，没有数据被命中。
• 运算符优先级错误：AND优先级高于OR，你原有的写法逻辑实际为(name匹配) OR (brand匹配 AND source='Added')，会误删source为Orig但name匹配的行，和你预期的(name匹配 OR brand匹配) AND source='Added'逻辑不符。

方案1：修正子查询返回数组类型+调整逻辑优先级 #

直接用ARRAY_AGG返回数组即可，不需要手动拼接字符串转数组格式：

DELETE FROM my_table
WHERE 
  (
    lower(name) ~~ ANY(
      SELECT ARRAY_AGG(DISTINCT '%' || lower(brand) || '%')
      FROM my_table
      WHERE source = 'Orig'
    )
    OR lower(brand) ~~ ANY(
      SELECT ARRAY_AGG(DISTINCT '%' || lower(brand) || '%')
      FROM my_table
      WHERE source = 'Orig'
    )
  )
  AND source = 'Added';

方案2：用CTE优化避免重复子查询 #

如果数据量较大，可以用CTE提前生成匹配数组，只查询一次orig数据：

WITH orig_patterns AS (
  SELECT ARRAY_AGG(DISTINCT '%' || lower(brand) || '%') AS patterns
  FROM my_table
  WHERE source = 'Orig'
)
DELETE FROM my_table
USING orig_patterns
WHERE 
  (lower(name) ~~ ANY(patterns) OR lower(brand) ~~ ANY(patterns))
  AND source = 'Added';

方案3：用EXISTS写法逻辑更直观 #

不需要数组操作的写法，可读性更高，也能避免类型问题：

DELETE FROM my_table t_add
WHERE 
  t_add.source = 'Added'
  AND EXISTS (
    SELECT 1 FROM my_table t_orig
    WHERE t_orig.source = 'Orig'
    AND (
      lower(t_add.name) LIKE '%' || lower(t_orig.brand) || '%'
      OR lower(t_add.brand) LIKE '%' || lower(t_orig.brand) || '%'
    )
  );

内容的提问来源于stack exchange，提问作者Viktor Kachmaryk UA