PostgreSQL列求和报错：function sum(text) does not exist 技术问询

错误原因 #

你用->>运算符从JSON中提取的numberOfHouseholds是文本类型，而sum()函数仅支持对数值类型（如integer、numeric）进行计算，因此报错提示找不到匹配的sum(text)函数。

修复后的SQL代码 #

方法1：提取字段时直接转换类型 #

在提取字段后添加类型转换，若字段是整数则转成integer：

SELECT sum(A.number_Of_Households)
FROM (SELECT 
  
  (json_data->'benefits' ->> 'numberOfHouseholds')::integer as number_Of_Households
  
FROM ccbc_public.application a 
    inner join ccbc_public.application_form_data afd on a.id = afd.application_id
    inner join ccbc_public.form_data f on f.id = afd.form_data_id
    where ccbc_public.application_status(a) <> 'draft') As A

如果字段包含小数，可转成numeric类型：

(json_data->'benefits' ->> 'numberOfHouseholds')::numeric as number_Of_Households

方法2：在sum函数内部转换类型 #

也可以直接在sum()中对字段做类型转换：

SELECT sum((A.number_Of_Households)::integer)
FROM (SELECT 
  
  json_data->'benefits' ->> 'numberOfHouseholds' as number_Of_Households
  
FROM ccbc_public.application a 
    inner join ccbc_public.application_form_data afd on a.id = afd.application_id
    inner join ccbc_public.form_data f on f.id = afd.form_data_id
    where ccbc_public.application_status(a) <> 'draft') As A

额外处理建议 #

如果JSON中的numberOfHouseholds可能存在非数值内容（如null、非数字字符串），可以用条件语句避免转换失败：

SELECT sum(CASE WHEN A.number_Of_Households ~ '^[0-9]+$' THEN A.number_Of_Households::integer ELSE 0 END)
FROM (SELECT 
  
  json_data->'benefits' ->> 'numberOfHouseholds' as number_Of_Households
  
FROM ccbc_public.application a 
    inner join ccbc_public.application_form_data afd on a.id = afd.application_id
    inner join ccbc_public.form_data f on f.id = afd.form_data_id
    where ccbc_public.application_status(a) <> 'draft') As A

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