PostgreSQL修改经纬度字段类型报错，SELECT正常ALTER异常求助

你的REGEXP_REPLACE("LATITUDE",',','.')仅替换了字符串中的第一个逗号，但表中存在包含多个逗号的异常值（比如报错提示的"3.890,225,016"）。替换后这个值会变成"3.890.225,016"，这种包含多个小数点和剩余逗号的格式无法转换为float类型，直接导致ALTER语句报错。

而SELECT语句大概率只是返回了部分格式正常（仅含一个逗号）的数据，或者你恰好没查询到那条异常数据，所以看起来执行正常。

1. 定位异常数据：先找出所有包含多个逗号的经纬度值，明确数据的实际含义：

SELECT "LATITUDE", "LONGITUDE" FROM col_name 
WHERE "LATITUDE" LIKE '%,%,%' OR "LONGITUDE" LIKE '%,%,%';

2. 修正异常值：根据业务逻辑处理这些异常数据：
   • 如果是输入错误（多打了逗号），直接修正为单逗号格式（如3.890225016）；
   • 如果是千分位分隔格式被错误输入（比如3,890,225.016被写成3.890,225,016），需先清理多余逗号，再转换为正确小数格式。
3. 调整ALTER语句：如果业务允许全局替换所有逗号为小数点，可使用带全局替换参数的语句：

ALTER TABLE col_name ALTER COLUMN "LATITUDE" TYPE float 
USING REGEXP_REPLACE("LATITUDE", ',', '.', 'g')::float;

注意：REGEXP_REPLACE的第四个参数'g'表示全局替换，会替换字符串中所有逗号，请确认这种转换符合业务数据的实际意义，避免错误转换。

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