PostgreSQL 14中如何确保character varying[]字段元素唯一性？

针对你用character varying[]字段存储多对多关联ID的场景，以下几种方案可以保证数组内无重复元素：

1. 插入/更新时主动去重 #

利用PostgreSQL内置函数在写入数据时直接处理重复项：

插入场景 #

使用array_distinct（PostgreSQL 13及以上版本支持）对输入数组去重后插入：

INSERT INTO records (tags)
SELECT array_distinct(ARRAY['tag_1', 'tag_2', 'tag_1']::character varying[]);

更新场景 #

拼接新元素后自动去重：

UPDATE records
SET tags = array_distinct(tags || ARRAY['new_tag']::character varying[])
WHERE id = 1;

注：||是PostgreSQL的数组拼接运算符，array_distinct会自动移除数组内的重复值。

2. 用触发器强制表层面约束 #

如果需要从表结构层面确保数组永远无重复，可以创建触发器在写入前自动处理：

第一步：创建触发器函数 #

CREATE OR REPLACE FUNCTION ensure_tags_unique()
RETURNS TRIGGER AS $$
BEGIN
  NEW.tags = array_distinct(NEW.tags);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

第二步：绑定触发器到records表 #

CREATE TRIGGER trigger_records_tags_unique
BEFORE INSERT OR UPDATE OF tags ON records
FOR EACH ROW EXECUTE FUNCTION ensure_tags_unique();

之后每次插入或更新tags字段时，都会自动去重，避免重复元素进入数组。

3. 重构为标准多对多表结构（推荐长期方案） #

虽然用数组存储关联ID看似便捷，但PostgreSQL更推荐用中间关联表实现多对多关系，这种方式天然避免重复，还能提升查询效率和可维护性：

• 保留records表（主键record_id）
• 新建tags表（主键tag_id，字段tag_name）
• 新建中间表record_tags，用record_id+tag_id作为联合主键，同时设置外键分别关联两张主表

通过联合主键的唯一性约束，天然不会出现重复的关联关系，后续的查询、统计和维护也更灵活。

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