PostgreSQL中为varchar数组添加元素时去重的实现方法

问题场景 #

现有表结构：

CREATE TABLE fun (
    id uuid not null,
    tag varchar[] NOT NULL,
    CONSTRAINT fun_pkey PRIMARY KEY(id, tag)
);

CREATE UNIQUE INDEX idx_fun_id ON fun USING btree (id);

已插入数据：

insert into fun (id, tag)
values('d7f17de9-c1e9-47ba-9e3d-cd1021c644d2', array['123','234'])

执行常规数组拼接更新时，会出现重复元素：

update fun
set tag = tag || array['234','345']
where id = 'd7f17de9-c1e9-47ba-9e3d-cd1021c644d2'

结果tag变为["123", "234", "234", "345"]，期望得到["123", "234", "345"]的去重结果。

解决方案 #

方法1：通过行集合并去重 #

将原数组和待添加数组展开为行，取并集后重新聚合为数组：

update fun
set tag = array(
    select unnest(tag)
    union
    select unnest(array['234','345'])
)
where id = 'd7f17de9-c1e9-47ba-9e3d-cd1021c644d2';

说明：union操作会自动剔除重复行，最终通过array()将去重后的行转回数组。

方法2：仅追加不存在的元素（性能更优） #

筛选出待添加数组中未在原数组出现的元素，再和原数组拼接：

update fun
set tag = tag || array(
    select elem
    from unnest(array['234','345']) elem
    where elem <> all(tag)
)
where id = 'd7f17de9-c1e9-47ba-9e3d-cd1021c644d2';

说明：

• unnest()将待添加数组拆分为单行元素
• elem <> all(tag)判断元素是否不在原数组内
• 最后用||拼接原数组和筛选后的新元素数组，避免重复

方法3：使用内置并集函数（PostgreSQL 11+） #

如果你的PostgreSQL版本是11及以上，可直接使用array_union内置函数，它会返回两个数组的去重并集：

update fun
set tag = array_union(tag, array['234','345'])
where id = 'd7f17de9-c1e9-47ba-9e3d-cd1021c644d2';

说明：array_union是PostgreSQL为数组提供的原生去重合并方法，代码更简洁。

验证结果 #

执行任一方案后，查询数据：

select tag from fun where id = 'd7f17de9-c1e9-47ba-9e3d-cd1021c644d2';

返回结果为["123", "234", "345"]，符合预期。

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