PostgreSQL中如何将varchar类型列转换为numeric或integer类型

1. 先检查数据有效性 #

转换前必须确认varchar类型的price列中所有值都能转为数字，否则转换会失败。执行以下查询找出不符合格式的脏数据：

SELECT price FROM cars WHERE price !~ '^[0-9]+(\.[0-9]+)?$';

• 正则说明：匹配整数（如10000）或小数（如12345.67）格式的字符串
• 对查询出的脏数据，根据业务需求修正（如补全数字、删除无效行）后再进行转换

2. 直接转换数据类型 #

转为integer类型（仅适用于所有price值都是整数的场景）

ALTER TABLE cars ALTER COLUMN price TYPE integer USING price::integer;

转为numeric类型（支持整数和小数）

ALTER TABLE cars ALTER COLUMN price TYPE numeric USING price::numeric;

如果price值中包含首尾空格，需要先去除空格再转换：

ALTER TABLE cars ALTER COLUMN price TYPE numeric USING trim(price)::numeric;

3. 处理含无效值的情况 #

如果无法提前清理脏数据，可通过CASE语句将无效值转为NULL（或指定默认值），避免转换报错：

ALTER TABLE cars ALTER COLUMN price TYPE numeric 
USING CASE 
    WHEN price ~ '^[0-9]+(\.[0-9]+)?$' THEN price::numeric 
    ELSE NULL  -- 也可替换为业务允许的默认值，如0
END;

注意事项 #

• 操作前务必备份表数据，防止转换过程中数据丢失
• 大表转换会锁表，建议在业务低峰期执行
• 若price列上存在索引、约束或触发器，转换后需检查并重建相关依赖

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