Преобразуйте строку в число, интерпретируя нулевую или пустую строку как 0

У меня есть таблица Postgres со строковым столбцом, содержащим числовые значения. Мне нужно преобразовать эти строки в числа для математики, но мне нужны оба значения NULL, а также пустые строки, которые будут интерпретироваться как 0.

Я могу преобразовать пустые строки в нулевые значения:

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

(1 row)

И я могу преобразовать нулевые значения в 0:

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

И я могу преобразовать строки в числа:

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

Но когда я пытаюсь объединить эти методы, я получаю ошибки:

# 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;

Что я делаю неправильно?

Ответ 1

Типы значений должны быть согласованными; объединение пустой строки в 0 означает, что вы не можете сравнить ее с null в nullif. Поэтому любая из этих работ:

# 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)

Ответ 2

Вы также можете использовать

cast(
    case
        when coalesce(orig, '') = '' then '0'
        else orig
    end
    as float
)

Вы также можете развернуть это немного, так как вы все равно начинаете довольно многословно:

cast(
    case
        when orig is null then '0'
        when orig = '' then '0'
        else orig
    end
    as float
)

или вы можете положить литье внутри CASE:

case
    when coalesce(orig, '') = '' then 0.0
    else cast(orig as float)
end

CASE упрощает учет любых других особых условий, это также кажется более ясным выражением логической ИМО. OTOH, личный вкус и все такое.

Ответ 3

Собственно, вы можете использовать NULL для int, вы просто не можете использовать пустую строку для int. Предполагая, что вам нужен NULL в новом столбце, если data1 содержит пустую строку или NULL, вы можете сделать что-то вроде этого:

UPDATE table SET data2 = cast(nullif(data1, '') AS int);

или

UPDATE table SET data2 = nullif(data1, '')::int;

Ссылка