PostgreSQL：COALESCE NULLの場合に代わりの値を返す方法

今回はPostgreSQLのCOALESCEを使い、NULLの場合に代わりの値を表示します。取得したデータの意味を整理します。

所要時間は15分ほどです。
それでは、さっそく始めていきましょう！

用語と到達目標

NULLは値がないことを表します。COALESCEは、指定した引数を左から順に確認し、最初のNULLではない値を返すSQLの式です。引数とは、括弧の中に指定する値や式のことです。

COALESCE(値1, 値2, ...)

すべての引数がNULLなら、結果もNULLです。また、引数は共通のデータ型として扱える必要があります。

今回の目標は、NULLの場合にだけ代わりの値を表示し、元のデータとSELECTの結果を区別できることです。

演習①：数値で動作を確認する

次のSQLを実行しました。

SELECT
    COALESCE(NULL, 100) AS fallback_value,
    COALESCE(50, 100) AS original_value,
    COALESCE(NULL, NULL, 200) AS third_value;

実行結果は次の1行でした。

fallback_value	original_value	third_value
100	50	200

COALESCE(NULL, 100)は最初の引数がNULLなので、次の100を返します。COALESCE(50, 100)は最初の50がNULLではないため、50を返します。COALESCE(NULL, NULL, 200)は最初の2つがNULLなので、3番目の200を返します。

常に最後の引数を返すわけではありません。左から見て最初に見つかった、NULLではない値が結果になります。

ゼロは代わりの値に置き換わるか

確認問題として、次の結果を考えました。

COALESCE(0, 100)

回答は「0」で、正解でした。0は数値の値であり、NULLではありません。そのため、最初の0が結果になります。この確認は予想を回答したもので、追加の実行結果として記録したものではありません。

COALESCEは、ゼロなどを一律に置き換える式ではありません。NULLかどうかで返す値を決めます。

演習②：施設の閉業日に表示用ラベルを付ける

取得元はmobility.poisです。1行が1施設を表します。poi_idは施設ID、poi_nameは施設名、closed_dateは閉業日を表す日付型の列です。

今回は施設ID1・39・41を取得し、閉業日がNULLの場合に「未登録」と表示しました。

SELECT
    poi_id,
    poi_name,
    closed_date,
    COALESCE(closed_date::TEXT, '未登録') AS closed_date_label
FROM mobility.pois
WHERE poi_id IN (1, 39, 41)
ORDER BY poi_id;

closed_date_labelは、SELECTの結果に付けた列名です。テーブルに新しい列を追加する指定ではありません。

実行結果は次の3行でした。

poi_id	poi_name	closed_date	closed_date_label
1	Tokyo_station_001	NULL（画面では空欄）	未登録
39	Omiya_hotel_039	2020-02-24	2020-02-24
41	Tokyo_station_041	2023-09-26	2023-09-26

ID1では閉業日がNULLなので、代わりの文字列が表示されました。ID39・41では閉業日が入力されているため、その日付を文字列に変換した結果が表示されました。

日付を文字列に変換する理由

closed_date::TEXTは、閉業日を文字列型へ変換する記述です。日付がNULLの場合、変換した結果もNULLになります。

「未登録」は日付ではなく文字列です。そこで、COALESCEに渡す最初の式も文字列型にし、返す値の型をそろえています。

元のclosed_dateは日付型のままです。一方、今回作ったclosed_date_labelは表示用の文字列です。日付を文字列に変換した結果が同じ見た目でも、データ型は区別します。

表示用ラベルとデータの意味を分ける

今回の「未登録」は、NULLの場合に表示するために指定したラベルです。NULLになった理由まで判定したものではありません。閉業日が不明なのか、未入力なのかなどは、データの定義や運用ルールで確認する必要があります。

特に、NULLに「営業中」というラベルを付けると、SQLで確認していない意味を加えてしまうことがあります。今回は、営業状態を判定するSQLではありません。

SELECTで保存済みの値は変わらない

「このSELECTを実行すると、テーブル内のNULL自体が未登録に書き換わるか」という確認には、「変わりません」と回答しました。

今回のSELECTは取得結果の値を計算して表示する操作です。COALESCEが返した文字列をテーブルへ保存する操作は行っていません。画像でも、元のclosed_dateと表示用のclosed_date_labelを並べて確認できました。

COALESCEという式そのものと、その式を使うSQL全体の操作を区別することが大切です。今回使ったSQLはデータの更新ではありません。

合格条件と学習記録

確認項目	確認できた内容
最初のNULLではない値を返すと理解する	100・50・200の実行結果を確認
0とNULLを区別する	COALESCE(0, 100)は0と回答
施設データに適用する	ID1は未登録、ID39・41は日付を表示
表示結果と保存値を区別する	テーブル内の値は変わらないと回答

まとめ

COALESCEは、左から順に見て最初のNULLではない値を返します。NULLの代わりに文字列を表示するときは、引数のデータ型をそろえます。

表示用ラベルは元の値や業務上の意味と区別します。今回のSELECTで変わったのは取得結果の表示であり、保存済みのデータではありません。