PostgreSQL执行ALTER TABLE时如何实现仅当约束不存在才添加

问题根因 #

PostgreSQL 12及以上版本才原生支持ALTER TABLE ADD CONSTRAINT的IF NOT EXISTS语法，低于该版本的数据库无法识别该语法，会直接执行报错。

处理方案 #

方案1：升级到PostgreSQL 12及以上版本 #

如果可以升级数据库版本，修正原有SQL的语法写法即可正常运行：

ALTER TABLE large_payout_clearance_slip_type_threshold_limit
DROP CONSTRAINT IF EXISTS large_payout_clearance_slip_type_threshold_limit_un_clearance_i;
ALTER TABLE public.large_payout_clearance_slip_type_threshold_limit 
ADD CONSTRAINT large_payout_clearance_slip_type_threshold_limit_slip_type_clearance_id UNIQUE (slip_type,large_payout_clearance_id) IF NOT EXISTS;

注意IF NOT EXISTS为SQL标准语法写法，PostgreSQL不区分语法关键字大小写，但统一大写可以避免不必要的歧义。

方案2：低版本兼容方案（无需升级） #

如果无法升级数据库，可以通过PG的匿名DO块先查询系统表判断约束是否存在，不存在再执行创建操作：

-- 先删除指定旧约束（如果存在）
ALTER TABLE large_payout_clearance_slip_type_threshold_limit
DROP CONSTRAINT IF EXISTS large_payout_clearance_slip_type_threshold_limit_un_clearance_i;
-- 匿名块判断目标约束是否存在
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'large_payout_clearance_slip_type_threshold_limit_slip_type_clearance_id' 
        AND conrelid = 'public.large_payout_clearance_slip_type_threshold_limit'::regclass
    ) THEN
        ALTER TABLE public.large_payout_clearance_slip_type_threshold_limit 
        ADD CONSTRAINT large_payout_clearance_slip_type_threshold_limit_slip_type_clearance_id UNIQUE (slip_type,large_payout_clearance_id);
    END IF;
END $$;

方案3：简化替代方案 #

如果不需要固定约束名，也可以用唯一索引替代唯一约束，创建索引的IF NOT EXISTS语法在PostgreSQL 9.5及以上版本就已支持，适配范围更广：

CREATE UNIQUE INDEX IF NOT EXISTS large_payout_clearance_slip_type_threshold_limit_slip_type_clearance_idx
ON public.large_payout_clearance_slip_type_threshold_limit (slip_type,large_payout_clearance_id);

PostgreSQL的唯一约束本质上就是通过唯一索引实现的，普通业务场景下二者效果完全一致，唯一区别是唯一约束属于SQL标准语法，无法设置填充因子等索引特有的参数。

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