PostgreSQL修改varchar列为UUID类型时如何删除无法转换的行

方案1：分步操作（推荐） #

操作前建议先备份全表数据，避免误删。

1. 先查询所有不符合UUID格式的行，确认待删除数据范围无误：

SELECT * FROM table_name 
WHERE col !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$';

2. 执行删除操作清理无效行：

DELETE FROM table_name 
WHERE col !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$';

3. 执行字段类型修改语句：

ALTER TABLE table_name
    ALTER COLUMN col TYPE UUID USING col::UUID;

方案2：事务原子操作 #

如果需要保证删除和修改操作要么同时成功要么同时回滚，可以把两步操作放在同一个事务中执行：

BEGIN;
-- 清理无效UUID格式行
DELETE FROM table_name 
WHERE col !~* '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$';
-- 修改字段类型
ALTER TABLE table_name
    ALTER COLUMN col TYPE UUID USING col::UUID;
COMMIT;

注意事项 #

• 上述正则仅适配标准带连字符的UUID格式，如果你的数据存在无连字符、带大括号等合法UUID变体，需要调整正则匹配规则。
• NULL值可以正常转换为UUID类型，不会被上述删除语句清理，如果需要同步删除NULL值，可以在DELETE的判断条件中补充OR col IS NULL。
• 表数据量较大时建议在业务低峰期操作，避免长时间锁表影响线上业务。

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