PostgreSQL无需删除约束将varchar类型主键外键转为UUID的方法

核心逻辑是利用PostgreSQL事务的原子性，让两张表的关联字段类型在事务提交时同时完成变更，绕过单条语句执行后的类型不匹配约束检查。

你单独执行任意一条ALTER语句时，数据库会立即校验外键约束的类型一致性，此时另一张关联字段还是varchar类型，所以触发报错。而将两个修改操作放在同一个事务中执行时，外键的类型一致性校验会延迟到事务提交时触发，此时两个字段已经同时完成uuid类型转换，完全满足约束要求，因此无需删除重建外键约束即可完成迁移。

具体操作代码 #

BEGIN;
-- 修改主键表字段类型
ALTER TABLE customer ALTER COLUMN id TYPE uuid USING id::uuid;
-- 修改外键表字段类型，注意order是SQL保留字，需要用双引号包裹
ALTER TABLE "order" ALTER COLUMN customer_id TYPE uuid USING customer_id::uuid;
COMMIT;

前置检查与注意事项 #

• 执行迁移前先验证所有待转换字段的值为合法UUID格式，避免转换失败回滚：

  -- 检查customer表id合法性
  SELECT id FROM customer WHERE id !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$';
  -- 检查order表customer_id合法性
  SELECT customer_id FROM "order" WHERE customer_id !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$';
  

  以上查询如果返回结果，说明存在不符合UUID格式的值，需要先修正异常数据后再执行迁移。
• 迁移前务必做好全量数据备份。
• 如果表数据量较大，建议在业务低峰期执行操作，避免锁表影响正常业务访问。

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