将字符串转换为数字，将null或空字符串解释为0 [英] Cast string to number, interpreting null or empty string as 0

问题描述

我有一个Postgres表，该表带有一个带数字值的字符串列。我需要将这些字符串转换为数字以进行数学运算，但我需要同时使用 NULL 值和空字符串以将其解释为 0 。

I have a Postgres table with a string column carrying numeric values. I need to convert these strings to numbers for math, but I need both NULL values as well as empty strings to be interpreted as 0.

我可以将空字符串转换为空值：

# select nullif('','');
 nullif 
--------

(1 row)

我可以将空值转换为 0 ：

# select coalesce(NULL,0);
 coalesce 
----------
        0
(1 row)

我可以将字符串转换为数字：

# select cast('3' as float);
 float8 
--------
      3
(1 row)

但是当我尝试结合使用这些技术时，会出现错误：

But when I try to combine these techniques, I get errors:

# select cast( nullif( coalesce('',0), '') as float);
ERROR:  invalid input syntax for integer: ""
LINE 1: select cast( nullif( coalesce('',0), '') as float);

# select coalesce(nullif('3',''),4) as hi;
ERROR:  COALESCE types text and integer cannot be matched
LINE 1: select coalesce(nullif('3',''),4) as hi;

我在做什么错了？

推荐答案

值的类型必须保持一致；将空字符串合并为0表示您无法将其与 nullif 中的 null 进行比较。因此，这些工作之一：

The types of values need to be consistent; coalescing the empty string to a 0 means that you cannot then compare it to null in the nullif. So either of these works:

# create table tests (orig varchar);
CREATE TABLE

# insert into tests (orig) values ('1'), (''), (NULL), ('0');
INSERT 0 4


# select orig, cast(coalesce(nullif(orig,''),'0') as float) as result from tests;
 orig | result 
------+--------
    1 |      1
      |      0
      |      0
    0 |      0
(4 rows)


# select orig, coalesce(cast(nullif(orig,'') as float),0) as result from tests;
 orig | result 
------+--------
 1    |      1
      |      0
      |      0
 0    |      0
(4 rows)

这篇关于将字符串转换为数字，将null或空字符串解释为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！