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

Question

У меня есть таблица 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;

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

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

Answer 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
    when coalesce(orig, '') = '' then 0.0
    else cast(orig as float)
end

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

Answer 3

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

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

или же

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

Ссылка

Answer 4

Убедитесь, что параметр запроса пуст (принимает пустую строку, пустую строку или значение):

SELECT CAST(TO_JSON(NULLIF(:myParameter, NULL)) AS VARCHAR) IS NULL OR
   CAST(TO_JSON(NULLIF(:myParameter, NULL)) AS VARCHAR) IN ('""');