Postgres使用ADD COLUMN IF NOT EXISTS意外重复创建唯一约束问题

为什么会出现重复创建唯一约束的情况 #

这是Postgres当前版本的设计行为：ALTER TABLE ADD COLUMN IF NOT EXISTS语句里的IF NOT EXISTS修饰符仅作用于「列创建」这个动作本身，不会同步校验列上的附加约束是否已经存在。

你将UNIQUE约束内联在ADD COLUMN语句中时，系统会自动为唯一约束生成递增后缀的名称（格式为test_table_name_key[n]），因为每次生成的约束名不重复，不会触发命名冲突校验，所以每次执行语句都会创建新的唯一约束，同时生成对应的独立索引。

而你拆分操作并主动指定约束名时，Postgres会先校验该名称的约束是否存在，存在就直接抛出错误，自然不会重复创建。ALTER COLUMN SET NOT NULL不存在这个问题，是因为NOT NULL是列本身的属性，没有独立的命名对象，重复执行只会直接跳过属性设置动作。

同一列上存在多个完全相同的索引的不良影响 #

• 写入性能下降：每次对该列执行插入、更新操作时，所有重复索引都需要同步修改，索引数量越多写入开销越大，大表场景下会显著降低写入吞吐量
• 存储空间浪费：每个索引都是独立的B树结构，会占用额外的磁盘空间，数据量越大浪费越明显，同时会拉长备份、数据恢复的耗时
• 优化器开销增加：执行计划生成阶段，优化器需要遍历所有可用索引做成本估算，冗余索引会增加不必要的计算开销
• 运维复杂度提升：排查慢查询、表结构问题时，冗余索引会干扰排查思路，也提高了误删有效索引的风险

推荐的幂等写法 #

如果要避免该问题，可使用带IF NOT EXISTS的拆分写法（PostgreSQL 11及以上版本支持）：

ALTER TABLE test_table ADD COLUMN IF NOT EXISTS name varchar(50);
ALTER TABLE test_table ADD CONSTRAINT IF NOT EXISTS test_table_name_key UNIQUE (name);

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