SQLのnullの扱いについて

あるカラムに文字列を追記したい為、update分で元のカラムにパイプ(||)を使って連結して更新を行った際、元カラムがNULLの場合意図しないNULLに更新されたので原因を調査してみました。

調査対象

Postgresql9.3.4

事象

例
update table_name 
set description = 'abcd' || description
where  
･･･
※descriptionがnullの場合、nullで更新されてしまう

原因

NULL値は不明の値を表しているため、不明な値との連結は行えない。 これは標準SQLに従った動作となります。

https://www.postgresql.jp/document/9.3/html/functions-comparison.html

解決策

COALESCE関数でNULLを置換する

COALESCE関数は、NULLでない自身の最初の引数を返します。全ての引数がNULLの場合にのみNULLが返されます。

COALESCE関数の使用例

SELECT COALESCE(description, short_description, '(none)') ...

descriptionがNULLでなければをそれ返します。そうでない場合(NULLの場合)は、
short_descriptionがNULLでなければそれを返します。そうでもない場合は(none)が帰ります。

結果、COALESCE関数で、NULLの場合スぺ―ス('')に置換することで解決しました。

update table_name 
set column_name = 'abcd' || coalesce(description,'')
where  
･･･
※descriptionがnullの場合、'abcd'として更新される

参考

COALESCE関数とは逆に、NULLに変換する必要がある場合、NULLIF関数をつかうと便利です。NULLIF関数の利用用途も交えて紹介します。

NULLIF関数の使用例

SELECT NULLIF(value1, value2) ...

value1がvalue2と等しい場合NULL値を返します。 等しくない場合はvalue1を返します。 

例　SQLの除算でゼロ除算エラーを回避する

例
SELECT
    column1 / column2
FROM
    table_name

※column2に0が設定されているとゼロ除算エラー(ERROR: division by zero)となる

ゼロ除算の回避方法

SELECT
    column1 / NULLIF(column2, 0)
FROM
    table_name

※column2が0の場合、ゼロ除算が回避され結果はNULLとなる

更に、結果にNULLを返したくない場合に、COALESCEと組み合わせることで回避できます

SELECT
    COALESCE(column1 / NULLIF(column2, 0), 0)
FROM
    table_name

※column2が0の場合、結果が0となる

よかったら、参考にしてみて下さい。