はじめに
OracleからPostgreSQLに移行した際、NULLと空文字の扱いの差で起こった問題を記録として残そうと思います。
この記事では Oracle側に合わせることを方針（空文字をNULLに変換する） として、起こった問題と解決策を書いていこうと思います。
NULL・空文字問題とは
Oracleでは''(空文字)とNULLはNULLとして扱いますが、
PostgreSQLではNULLのみNULLとして扱い、''(空文字)は空文字として扱います。
この違いのせいで移行した際、どちらに合わせるか方針を決めてSQL文を書き直すなどの必要があります。
問題1. 空文字が含まれるCASTによるinvalid input syntaxエラー
事例1
-- 変数Aには空文字('')が入っている
CAST(変数A AS FLOAT)
invalid input syntax for type double precision: ""
「FLOAT型(type double)に対する無効な入力やで」という意味
対策1
nullif関数 を使用して対策を行います。
nullif関数は第一引数と第二引数が等しい文字列であればNULLを返してくれます。
CASTしたい値が変数Aだとします。以下のようにすることで変数Aに空文字が入ればNULLが返り、そうでなければ変数Aの文字列がそのまま通ります。
CAST(nullif(変数A,'') AS FLOAT)
問題2. COALESCE関数通り抜け問題
事例2
COALESCE関数（OracleではNVL関数）はNULLをデフォルト値に置き換えるためによく使用されます。
例えば、以下のように変数BにNULLが来たら0に置き換える動作があったとします。変数Bの値が正しくNULLならば問題ないのですが、空文字だった場合はデフォルト値に置き換えることなくそのまま空文字を素通りさせてしまいます。
COALESCE(変数B,'0')
対策2
はい。またもや登場nullif関数くんです。
COALESCE(nullif(変数B,''),'0')
先ほども解説しましたが、nullif(変数B,'')で空文字をNULLに変換することで正しく置き換えが機能するようにしています。
問題3. 空文字を日付型に変換すると…
事例3
以下のようなSQLがあったとします。
TO_DATE(RPAD(変数C,12,'0'),'YYMMDDHH24MISS')
変数CにちゃんとNULLや数字の文字列が入れば問題ないのですが、空文字がはいると本来ならばNULLにしたいところ2000/01/01という値になってしまいます。
これは空文字をRPAD関数で0埋めした結果000000000000となるためですね。
ちなみにRPAD関数関係なく
TO_TIMESTAMP('','YYMMDDHH24MISS')
であった場合もNULLにはなりません。
対策3
3度目の登場、nullif関数です。説明は省きますが以下のようにすればOKです。
TO_DATE(RPAD(nullif(変数C,’’),12,'0'),'YYMMDDHH24MISS')
空文字をNULLにしてくれるので上手く行きます！
最後に
今回は全てnullifの力で乗り切りましたが、これが最適解ではないかもしれません。なんかいい感じの楽な方法があればいいんですけどねー。
nullifについては以下をみて。