Postgres SQL状态：22P02 [英] Postgres SQL state: 22P02

问题描述

我需要在Postgres中运行以下查询：

I need to run the following query in 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;

尽管将conversion_units设置为数字，这却给了我以下错误：

Despite setting conversion_units to numeric, it is giving me the following error:

ERROR:  invalid input syntax for type numeric: ""
********** Error **********

ERROR: invalid input syntax for type numeric: ""
SQL state: 22P02

值得注意的是，我已经进行了一些单元测试，当我运行此查询抵押贷款并删除贷款行时，工作良好。我已将问题隔离到贷款的 conversion_units :: numeric 中。除了通常的转换（如我在此处指定的那样）外，我不确定还有什么尝试。我通读了带有此错误的问题，但它们似乎并没有反映我的问题。任何帮助表示赞赏！谢谢！

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::numeric for 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!

推荐答案

显然， conversion_units 是一个可以容纳值的字符串可转换为数字。

Apparently conversion_units is a string which can hold values not convertible to numeric.

您的直接问题可以通过以下方式解决：

You immediate problem can be solved this way:

SUM(NULLIF(conversion_units, '')::numeric)

，但可以有其他值。

您可能会尝试使用regexp来匹配可转换字符串：

You might try to use regexp to match convertible strings:

SUM((CASE WHEN conversion_units ~ E'^\\d(?:\\.\\d)*$' THEN conversion_units END)::numeric)

这篇关于Postgres SQL状态：22P02的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！