Redshift报错：Invalid input syntax for type numeric 原因及修复方法

问题重现 #

执行以下查询时触发错误：

select
row_number() over(partition by nb.roadnumbercode
        order by sqrt(power((1.0*cast(ra.latitude as decimal(38,10)))-coalesce(nb.buildingcenterlatitude,0),2)
                +power((1.0*cast(ra.longitude as decimal(38,10)))-coalesce(nb.buildingcenterlongitude,0),2)
                )) rnum
from dmart.addresses ra join dmart.buildings nb on nb.roadnumbercode = ra.roadnamecode and nb.buildingcenterlatitude is not null and nb.buildingcenterlongitude is not null
limit 30; 

错误信息：ERROR: Invalid input syntax for type numeric

错误原因 #

• 核心问题是dmart.addresses表的latitude或longitude字段中存在无法转换为decimal类型的非数值内容，比如空字符串、字母、特殊符号，或者格式错误的文本（比如多个小数点）。Redshift执行cast(ra.latitude as decimal(38,10))时，遇到这类无效输入就会抛出语法错误。
• 虽然查询里过滤了nb表的经纬度非空，但ra表的对应字段只做了关联，没有做有效性校验——哪怕ra.latitude非空，也可能是不符合数值规范的无效值。

修复方案 #

1. 先排查无效数据 #

先找出ra表中经纬度字段的异常值，确认问题来源：

select latitude, longitude
from dmart.addresses
where not (latitude ~ '^-?\d+(\.\d+)?$') 
   or not (longitude ~ '^-?\d+(\.\d+)?$');

这个正则会匹配合法的正负数值（整数或小数），返回所有不符合规范的记录。

2. 修正查询语句（避免报错） #

用Redshift的try_cast函数替代cast——它在转换失败时返回null而非抛出错误，同时在关联条件里过滤掉无法转换的记录：

select
row_number() over(partition by nb.roadnumbercode
        order by sqrt(power((1.0*try_cast(ra.latitude as decimal(38,10)))-coalesce(nb.buildingcenterlatitude,0),2)
                +power((1.0*try_cast(ra.longitude as decimal(38,10)))-coalesce(nb.buildingcenterlongitude,0),2)
                )) rnum
from dmart.addresses ra 
join dmart.buildings nb 
    on nb.roadnumbercode = ra.roadnamecode 
    and nb.buildingcenterlatitude is not null 
    and nb.buildingcenterlongitude is not null
    -- 过滤ra表中无法转换为数值的经纬度记录
    and try_cast(ra.latitude as decimal(38,10)) is not null
    and try_cast(ra.longitude as decimal(38,10)) is not null
limit 30; 

3. 彻底修复（清理源数据） #

如果业务允许，直接修正dmart.addresses表的无效数据：

• 将非数值的经纬度内容更新为null，或者修正为合法的数值
• 之后再执行原查询即可正常运行

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