如何从PostgreSQL字符串列中移除多个指定字符串

原语句失效的原因 #

1. WHERE条件错误：current_ids IN('ABX1-003', 'ABX1-006', 'ABX1-007')是判断整个current_ids字段值等于某个单独ID，但实际字段是包含多个ID的字符串（如|ABX1-001|ABX1-002|...|），因此永远不会匹配到目标行，导致UPDATE无效果。
2. 正则表达式不严谨：直接匹配ID文本会导致两个问题：一是删除后会留下连续的||分隔符；二是如果存在类似ABX1-0033的ID，会被误匹配删除。

正确的UPDATE实现 #

下面的语句会同时处理current_ids和total_ids，精准删除目标ID并保持格式一致：

UPDATE candidates
SET
    current_ids = regexp_replace(current_ids, '\|(ABX1-003|ABX1-006|ABX1-007)\|', '|', 'g'),
    total_ids = regexp_replace(total_ids, '\|(ABX1-003|ABX1-006|ABX1-007)\|', '|', 'g')
WHERE
    current_ids ~ '\|(ABX1-003|ABX1-006|ABX1-007)\|'
    OR total_ids ~ '\|(ABX1-003|ABX1-006|ABX1-007)\|';

代码说明 #

• 正则表达式：\|(ABX1-003|ABX1-006|ABX1-007)\| 匹配被|包裹的完整目标ID，确保只删除指定的ID，避免误删类似前缀的ID；替换为单个|，保证字符串格式始终是|ID1|ID2|...|的结构，不会出现连续分隔符。
• WHERE子句：使用正则匹配~判断字段中是否包含目标ID，只更新有需要删除内容的行，避免无意义的更新操作。
• 批量更新：同时处理current_ids和total_ids，一次完成所有需要的修改。

验证效果 #

执行后，原数据：

current_ids: '|ABX1-001|ABX1-002|ABX1-003|ABX1-004|ABX1-005|ABX1-006|'
total_ids: '|ABX1-001|ABX1-002|ABX1-003|ABX1-004|ABX1-004|ABX1-006|ABX1-007|'

会被更新为：

current_ids: '|ABX1-001|ABX1-002|ABX1-004|ABX1-005|'
total_ids: '|ABX1-001|ABX1-002|ABX1-004|ABX1-004|'

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