Postgres 13.4 如何修改已有DOMAIN的CHECK约束而无需删除重建？

核心结论 #

PostgreSQL 13.4 不支持直接修改已有DOMAIN的CHECK约束内容，但你不需要删除整个DOMAIN，也不需要删除依赖该DOMAIN的字段、函数等关联对象，仅需删除旧约束后新建约束即可完成修改，操作成本远低于你预期。

针对你的场景的具体操作步骤 #

你可以直接执行以下两条SQL完成需求，全程不会影响已经使用该域的业务对象：

1. 删除原有旧CHECK约束

ALTER DOMAIN domains.user_name DROP CONSTRAINT user_name_legal_values;

2. 新增带user_analytics的新CHECK约束

ALTER DOMAIN domains.user_name ADD CONSTRAINT user_name_legal_values
CHECK(
    VALUE IN (
        'postgres',
        'dbadmin',
        'user_bender',
        'user_cleanup',
        'user_domo_pull',
        'user_analytics'
    )
);

注意事项 #

• 上述操作建议放在同一个事务中执行，避免删除约束到新建约束的窗口有非法数据写入，事务会锁定域的元数据，全程不会出现校验真空期。
• 新增约束时PostgreSQL会自动校验所有已使用该域的历史数据是否符合新约束规则，如果存在不符合的历史数据，新增约束会失败，你可以选择先清理不符合规则的历史数据，或者添加NOT VALID参数跳过历史数据校验（仅对后续新写入/更新的数据生效），示例如下：

ALTER DOMAIN domains.user_name ADD CONSTRAINT user_name_legal_values
CHECK(
    VALUE IN (
        'postgres',
        'dbadmin',
        'user_bender',
        'user_cleanup',
        'user_domo_pull',
        'user_analytics'
    )
) NOT VALID;

长期优化建议 #

如果后续你需要频繁调整该允许值列表，建议改用小型查找表实现约束：

1. 新建存储允许用户名的查找表

CREATE TABLE domains.valid_user_names (
    user_name citext PRIMARY KEY
);
-- 插入初始允许值
INSERT INTO domains.valid_user_names VALUES
('postgres'),('dbadmin'),('user_bender'),('user_cleanup'),('user_domo_pull'),('user_analytics');

2. 修改域的CHECK约束关联查找表

ALTER DOMAIN domains.user_name DROP CONSTRAINT user_name_legal_values;
ALTER DOMAIN domains.user_name ADD CONSTRAINT user_name_legal_values
CHECK (VALUE IN (SELECT user_name FROM domains.valid_user_names));

后续需要增减允许值时，仅需对valid_user_names表执行INSERT/DELETE操作即可，完全不需要修改域或约束的定义，更适合频繁迭代的场景。

内容的提问来源于stack exchange，提问作者Morris de Oryx