PostgreSQL中如何更新TEXT数组字段并修剪元素空格

你可以直接用unnest拆分数组元素，结合array_agg或array()构造器生成修剪后的数组，完成更新操作，比你之前的查询写法更简洁：

方法一：子查询聚合更新 #

UPDATE institutional_review_boards irb
SET attached_sites = (
    SELECT array_agg(trim(e))
    FROM unnest(irb.attached_sites) e
);

这个语句会遍历每行的attached_sites数组，把每个元素拆出来修剪空格，再重新聚合成数组赋值回原列。

方法二：用array()构造器简化写法 #

UPDATE institutional_review_boards
SET attached_sites = array(
    SELECT trim(elem)
    FROM unnest(attached_sites) elem
);

效果和上面完全一致，语法更紧凑。

处理NULL或空数组的情况 #

如果你的列存在NULL值或者空数组，不想改动它们，可以加个条件判断：

UPDATE institutional_review_boards
SET attached_sites = CASE
    WHEN attached_sites IS NOT NULL THEN array(SELECT trim(elem) FROM unnest(attached_sites) elem)
    ELSE attached_sites
END;

你之前的查询用了row_number()分组其实没必要——我们可以直接针对单行的数组进行拆包和聚合，不需要全局的行号分组逻辑。

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