PostgreSQL中利用string_to_array实现表更新报错求解

你的SQL语句存在两个核心问题：

• 子查询select name from table2 where origin='UK'若返回多条记录，直接用它与any(...)做相等比较会触发逻辑错误，甚至语法报错（因为单行子查询返回了多行结果）。
• 逻辑判断方向颠倒，应该检查table1.names拆分后的数组元素是否存在于table2的UK名称集合中，而非反过来。

以下是几种可行的修正方案：

方案1：使用EXISTS子查询（推荐，性能稳定） #

这种写法逻辑清晰，适合大多数场景，尤其是当table2数据量较大时：

-- 仅更新符合条件的记录为true
UPDATE table1 AS t1
SET result = true
WHERE EXISTS (
    SELECT 1
    FROM table2 AS t2
    WHERE t2.origin = 'UK'
      AND t2.name = ANY(string_to_array(t1.names, ','))
);

-- 如果需要将不符合条件的记录统一设为false，用CASE语句
UPDATE table1 AS t1
SET result = CASE
    WHEN EXISTS (
        SELECT 1
        FROM table2 AS t2
        WHERE t2.origin = 'UK'
          AND t2.name = ANY(string_to_array(t1.names, ','))
    ) THEN true
    ELSE false
END;

方案2：使用数组重叠运算符（简洁高效） #

PostgreSQL支持&&运算符判断两个数组是否有重叠元素，写法非常简洁：

UPDATE table1 AS t1
SET result = string_to_array(t1.names, ',') && ARRAY(SELECT name FROM table2 WHERE origin='UK');

方案3：数组交集判断（适合需要明确交集的场景） #

利用array_intersect函数获取两个数组的交集，再判断交集是否非空：

UPDATE table1 AS t1
SET result = (
    SELECT array_length(
        array_intersect(
            string_to_array(t1.names, ','),
            ARRAY(SELECT name FROM table2 WHERE origin='UK')
        ), 1
    ) > 0
);

注：array_intersect要求PostgreSQL 9.6及以上版本。

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