PostgreSQL中SUM转换VARCHAR为int时报double precision语法错误求助

错误根源 #

你遇到的invalid input syntax for type double precision: ""错误，是因为covid_vacc.new_vaccinations列是VARCHAR类型，其中包含空字符串（""），而PostgreSQL无法将空字符串直接转换为整数类型，导致强制转换时失败。CSV导入时经常会把空值识别为空字符串，而非SQL标准的NULL。

临时解决（无需修改表结构） #

直接在查询中先把空字符串转为NULL，再进行转换和求和——聚合函数SUM会自动忽略NULL值，不影响最终计算结果。

方案1：用CASE语句处理

select 
  covid_deaths.continent, 
  covid_deaths.location, 
  covid_deaths.date, 
  covid_deaths.population, 
  covid_vacc.new_vaccinations,
  SUM(CASE WHEN covid_vacc.new_vaccinations = '' THEN NULL ELSE covid_vacc.new_vaccinations::int END) 
    over (partition by covid_deaths.location 
          order by covid_deaths.location, covid_deaths.date) as RollingPeopleVaccinated
from covid_deaths
join covid_vacc
  on covid_deaths.location = covid_vacc.location 
  and covid_deaths.date::date = covid_vacc.date::date

方案2：用NULLIF函数简化（更简洁）

NULLIF(a, b)会在a等于b时返回NULL，否则返回a，刚好适配你的场景：

select 
  covid_deaths.continent, 
  covid_deaths.location, 
  covid_deaths.date, 
  covid_deaths.population, 
  covid_vacc.new_vaccinations,
  SUM(NULLIF(covid_vacc.new_vaccinations, '')::int) 
    over (partition by covid_deaths.location 
          order by covid_deaths.location, covid_deaths.date) as RollingPeopleVaccinated
from covid_deaths
join covid_vacc
  on covid_deaths.location = covid_vacc.location 
  and covid_deaths.date::date = covid_vacc.date::date

彻底修复（修改表结构，一劳永逸） #

如果不想每次查询都处理空字符串，可以直接修正表的列类型，步骤如下：

1. 先把所有空字符串替换为NULL：

UPDATE covid_vacc 
SET new_vaccinations = NULL 
WHERE new_vaccinations = '';

2. 修改列类型为整数（如果存在非数字的脏数据，这一步会报错，需要先清理）：

ALTER TABLE covid_vacc 
ALTER COLUMN new_vaccinations TYPE INTEGER USING new_vaccinations::INTEGER;

3. （可选）提前排查脏数据
   如果执行ALTER时报错，说明列中存在非数字的内容，先查询出来清理：

-- 找出所有非空且不是纯数字的记录
SELECT new_vaccinations 
FROM covid_vacc 
WHERE new_vaccinations ~ '[^0-9]' AND new_vaccinations != '';

针对这些脏数据，要么删除要么修正，再重新执行ALTER语句。

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