PostgreSQL文本处理后转类型求和报错SQL state:22P02如何解决？

问题背景 #

源数据带€符号，导入PostgreSQL时只能按text类型存储，样例数据如下：

original data sample:
    "€31.51"
    "€0.10"
    "€24.23"

首次尝试及报错 #

尝试用字符串函数去除€符号后转NUMERIC类型再求和，执行如下SQL时报错SQL state: 22P02：

SELECT sum(coalesce(CAST(split_part(revenue_eur, '€', 2) as NUMERIC),'0'))
FROM revenue_test;

仅单独执行拆分字符串的查询可以正常运行：

SELECT coalesce(split_part(revenue_eur, '€', 2),'0')
FROM revenue_test;

尝试用子查询实现也失败，需要解决两个问题：

• 如何正确完成带符号金额的求和？
• 导入数据时有没有办法直接去除€符号并存为数值类型？

环境说明 #

使用PostgreSQL 12，通过pgAdmin4导入CSV文件，共85k行数据。最初尝试用COPY语句导入时报权限错误，检查文件权限无果后改用pgAdmin右键导入功能完成操作。

问题根因及修复方案 #

报错原因 #

部分导入的数值带千分位逗号分隔符，未处理时无法完成字符串到数值的类型转换。

最终实现代码 #

WITH test AS
(SELECT translate(revenue_eur, '€,', '')::float as eur
FROM revenue_test)

SELECT sum(coalesce(eur, '0'))
from test;

导入时直接转换的方案 #

如果后续需要重新导入数据，可通过临时表中转的方式实现直接存储数值类型，无需后续额外转换：

1. 先创建临时表，将金额字段设为text类型，使用pgAdmin导入功能或COPY语句将原始数据导入临时表
2. 从临时表往正式表迁移数据时完成清洗转换：

INSERT INTO 正式表名 (金额字段名, 其他字段名...)
SELECT translate(临时表金额字段, '€,', '')::numeric, 其他字段... 
FROM 临时表名;

如果要使用COPY语句规避权限问题，可改用COPY ... FROM STDIN的方式，适配pgAdmin等客户端的执行权限。

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