この記事はNTTコムウェア AdventCalendar 2023 16日目の記事です。
はじめに
NTTコムウェアの星合です。
普段は社内でデータベース関連のサポートやトラブル対応、性能検証などをしています。
最近では社外でのコミュニティ活動にも力を入れており、Pgpool-IIのコミッタやPostgreSQLの開発も行ってます。
今回は、小ネタとして知らない人が多そうなPostgreSQLでNULLの値を = 演算子で評価する方法を紹介します。
なお、SQL標準では 値 = NULLでは真偽値を得られないのが正解です。
そのため、実際のシステム開発ではIS NULL句を使うようにしてください。
NULLについて
プログラムで扱いの難しいデータの一つとして、NULL (＝何もない状態や不明な状態を表す値) があるかと思います。
PostgreSQLデータベースでは、評価条件にNULLが含まれると演算結果はTrue or False ではなく全てNULLという結果になります。
[postgres@localhost ~]$ psql test
psql (16.1)
"help"でヘルプを表示します。
test=# \pset null (null)
Null表示は"(null)"です。
test=#
test=# SELECT 1 = NULL;
?column?
----------
(null)
(1 行)
test=# SELECT NULL = NULL;
?column?
----------
(null)
(1 行)
PostgreSQLのpsqlクライアントでは、以下のコマンドでNULLの表示形式を変更できます。
\pset null (null)
デフォルトではNULLは空欄で表現されるため、今回は見やすいように (null) という文字に変更してます。
そのため、NULL を判定するためには専用の IS NULL や IS NOT NULL条件で評価するのが通常です。
test=# SELECT NULL IS NULL;
?column?
----------
t
(1 行)
ですが、実はSQLの書き換え以外にも = の演算子そのままでNULLを評価する方法もあります。
transform_null_equalsパラメータ
PostgreSQLでは = 演算子に限り、NULLを比較できるようにするtransform_null_equals パラメータがあります。
こちらのパラメータをonにすると、 値 = NULL の計算を 値 IS NULL に変換して処理してくれるためNULLであるか判定することができます。
test=# set transform_null_equals = on;
SET
test=#
test=# SELECT NULL = NULL;
?column?
----------
t
(1 行)
ただし、= NULL の条件が変換されるのみとなっており、 != 演算子や IN(...) 条件、結合条件などはonにしても結果はNULLのままなので気を付けてください。
test=# SELECT NULL != NULL;
?column?
----------
(null)
(1 行)
test=# SELECT 404 IN (1,NULL,404);
?column?
----------
(null)
(1 行)
おわりに
ということで、知ってる人があまりいないであろう transform_null_equalsパラメータについての紹介でした。
本来はIS NULLを使うのが標準であり推奨となります。昔、一部の製品でこの標準に対応していなかった背景があり、代替案としてPostgreSQL側でこのパラメータが用意されていました。
また、回避策としても != 条件や IN(...) で使えないなど非常に使用ケースが限られる点から、やはり NULL を扱う場合には適切にアプリケーション設計を行う必要があります。
※記載されている会社名、製品名、サービス名は、各社の商標または登録商標です。