兼容Oracle的企业版Postgres：如何修改分区CHECK约束添加值？

嘿，我来帮你搞定这个问题！你用的是兼容Oracle的企业版Postgres（大概率是EDB Postgres Advanced Server），遇到的这些操作失败问题，本质是因为Postgres对分区的系统自动生成约束有特殊保护——这类约束是分区定义的核心部分，不能直接修改或删除，而且分区和主表的继承关系也是系统维护的，不能随便拆。下面给你两种可行的解决方案：

方法一：用Oracle兼容语法直接修改分区（推荐！） #

既然你用的是兼容Oracle的版本，直接用Oracle风格的分区修改命令就行，一步到位更新分区的取值范围，系统会自动帮你调整CHECK约束：

-- 把新值替换成你要加的两个数值，比如123和456
ALTER TABLE ALL_COUNTRIES MODIFY PARTITION COUNTRY ADD VALUES (123, 456);

这个命令会自动把新值加入到分区的允许取值列表里，不需要手动折腾约束或者分区，是最省心的方法。

方法二：原生Postgres风格的分步操作（兼容语法不可用时用） #

如果因为某些原因没法用上面的兼容语法，那就用原生Postgres的分离-修改-重新附加流程，步骤如下：

1. 先备份分区数据（千万别忘了这一步，防止数据丢失）：

   CREATE TABLE temp_country_data AS SELECT * FROM cdar_panel.all_countries_country;
   

2. 把分区从主表中分离出来：
   这一步会解除分区和主表的关联，让你可以自由修改这个表：

   ALTER TABLE cdar_panel.all_countries DETACH PARTITION cdar_panel.all_countries_country;
   

3. 删除旧的CHECK约束，创建包含新值的新约束：
   先确认原约束名称（你这里是all_countries_country_partition），然后执行：

   ALTER TABLE cdar_panel.all_countries_country DROP CONSTRAINT all_countries_country_partition;
   -- 把新值加到ARRAY里，比如这里加123和456
   ALTER TABLE cdar_panel.all_countries_country ADD CONSTRAINT all_countries_country_partition CHECK ((country_id = ANY (ARRAY['484'::numeric(20,0), '170'::numeric(20,0), '76'::numeric(20,0), '360'::numeric(20,0), '710'::numeric(20,0), '123'::numeric(20,0), '456'::numeric(20,0)])) AND country_id IS NOT NULL);
   

4. 把修改后的表重新附加为主表的分区：

   ALTER TABLE cdar_panel.all_countries ATTACH PARTITION cdar_panel.all_countries_country FOR VALUES IN (484, 170, 76, 360, 710, 123, 456);
   

5. 验证数据并清理临时表：
   确认数据没丢之后，就可以删掉临时备份了：

   SELECT COUNT(*) FROM cdar_panel.all_countries_country; -- 和备份前的数量对比
   DROP TABLE temp_country_data;
   

为啥你之前的操作都失败了？ #

• 无法修改约束：系统生成的分区CHECK约束是分区元数据的一部分，Postgres不允许直接修改它，因为要保证分区的取值范围和主表的分区规则严格一致。
• 无法DROP ONLY分区：分区是主表的继承子表，而且这个继承关系是系统维护的分区关联，不能用DROP ONLY直接删除，必须先分离分区才行。
• 无法加新约束再删旧约束：原约束是分区的标识性约束，Postgres会阻止删除它，除非先解除分区和主表的关联（也就是执行分离操作）。

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