postgresql Postgres SQL 状态：22P02
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31123872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres SQL state: 22P02
提问by user2573355
I need to run the following query in Postgres:
我需要在 Postgres 中运行以下查询：
select left(file_date, 10) as date, lob_name, devicesegment, sum(conversion_units::numeric) as units
from bac_search.dash_search_data
where (lob_name= 'Mortgage' and file_date::date between (CURRENT_DATE - INTERVAL '30 days') and CURRENT_DATE)
or (lob_name= 'Loans' and file_date::date between (CURRENT_DATE - INTERVAL '30 days') and CURRENT_DATE)
group by file_date, lob_name, devicesegment
order by file_date, lob_name, devicesegment;
Despite setting conversion_units to numeric, it is giving me the following error:
尽管将conversion_units设置为数字，但它给了我以下错误：
ERROR: invalid input syntax for type numeric: "" ********** Error ********** ERROR: invalid input syntax for type numeric: "" SQL state: 22P02
ERROR: invalid input syntax for type numeric: "" ********** Error ********** ERROR: invalid input syntax for type numeric: "" SQL state: 22P02
Of note, I've done some unit testing and when I run this query for Mortgage and delete the line for Loans, it works fine. I've isolated the problem to conversion_units::numericfor Loans. Besides the usual conversion (as I've specified here), I'm not sure what else to try. I read through the questions with this error, but they don't seem to mirror my problem. Any help is appreciated! Thanks!
值得注意的是，我已经完成了一些单元测试，当我为 Mortgage 运行此查询并删除 Loans 行时，它工作正常。我已将问题隔离到conversion_units::numericfor Loans。除了通常的转换（正如我在此处指定的那样），我不确定还可以尝试什么。我通读了带有此错误的问题，但它们似乎没有反映我的问题。任何帮助表示赞赏！谢谢！
回答by Quassnoi
Apparently conversion_unitsis a string which can hold values not convertible to numeric.
显然conversion_units是一个字符串，它可以保存不能转换为numeric.
You immediate problem can be solved this way:
你眼前的问题可以这样解决：
SUM(NULLIF(conversion_units, '')::numeric)
but there can be other values.
但可以有其他值。
You might try to use regexp to match convertible strings:
您可以尝试使用正则表达式来匹配可转换字符串：
SUM((CASE WHEN conversion_units ~ E'^\d(?:\.\d)*$' THEN conversion_units END)::numeric)
回答by jonatr
For future searchers:
对于未来的搜索者：
I got this error (Postgres SQL state: 22P02) while trying to import data from csv.
尝试从 csv 导入数据时出现此错误（Postgres SQL 状态：22P02）。
with this command:
用这个命令：
COPY my_end_table FROM 'D:\check\sample.csv' WITH CSV
从 'D:\check\sample.csv' 用 CSV 复制 my_end_table
Solution: turns out the csv should not contain headers....
解决方案：原来 csv 不应该包含标题....
回答by Youssri Abo Elseod
The problem in order column in csv.
中的 order 列中的问题csv。
The order in csvfile must be the same column name in copy syentext.
csv文件中的顺序必须与复制同义文本中的列名相同。
