SQL报错‘Numeric value 'Bronx' is not recognized’原因排查求助

核心错误原因 #

1. 隐式类型转换触发报错：WHERE子句中Boro NOT LIKE 0里的0是数值类型，但Boro为varchar字符串类型。数据库会尝试将Boro列的所有字符串值（比如'Bronx'）隐式转换为数值做比较，字符串无法转成数值就抛出了Numeric value 'Bronx' is not recognized错误。
2. 列顺序不匹配的潜在问题：INSERT语句的列顺序是"Total Inspections", Boro, Year，但SELECT语句的返回顺序是Boro, COUNT(*), YEAR(...)，这会导致数据插入错位——把Boro的字符串值插到整数类型的"Total Inspections"列，后续也会触发类型错误。

修正后的SQL代码 #

CREATE OR REPLACE SCHEMA Curated;

CREATE OR REPLACE TABLE CURATED.Insp (
  "Total Inspections" INTEGER,
  Boro VARCHAR(50),
  Year INTEGER
);

INSERT INTO curated.Insp ("Total Inspections", Boro, Year)
-- 调整SELECT列顺序，与INSERT目标列顺序对齐
SELECT COUNT(*) AS "Total Inspections", Boro, YEAR(CAST(Inspection_date AS DATE)) AS "Year"
FROM raw.inspection
WHERE YEAR(CAST(Inspection_date AS DATE)) >= 2000 
  -- 将数字0改为字符串'0'，做字符串精确匹配
  AND Boro != '0'
GROUP BY YEAR(CAST(Inspection_date AS DATE)), BORO
ORDER BY YEAR(CAST(Inspection_date AS DATE)) DESC;

额外优化建议 #

可以提前提取Inspection_date的转换逻辑，避免重复CAST操作，提升代码可读性：

WITH transformed_data AS (
  SELECT 
    Boro,
    YEAR(CAST(Inspection_date AS DATE)) AS inspection_year
  FROM raw.inspection
  WHERE YEAR(CAST(Inspection_date AS DATE)) >= 2000 
    AND Boro != '0'
)
INSERT INTO curated.Insp ("Total Inspections", Boro, Year)
SELECT COUNT(*), Boro, inspection_year
FROM transformed_data
GROUP BY inspection_year, BORO
ORDER BY inspection_year DESC;

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