PostgreSQLにおけるNVL関数の代替手段

NVL関数というものを持っているDBMSがいくつかある。
例えばOracle、例えばInformix。
しかしPostgreSQLにはNVL関数がない。そこで代わりに登場するのがCOALESCE関数である。

そもそもNVL関数とはなんじゃ？というと
「抽出した値がNULLの場合、代替値を返す」
ものとなる。

SELECT nvl(order_date," ") FROM sales;

みたいな感じで、NULLが返ってきたら代わりにスペースを出力する、という動きをする。

PostgreSQLにおける代替関数は先ほども書いたCOALESCE。

SELECT COALESCE(order_date," ") FROM sales;

これにて同一挙動をする。

ただ正確にはNVL関数は該当する値がNULLの場合の代替値を返すのに対して、CALESCEはNULL でない自身の最初の引数を返す、というのが仕様であり引数は列挙できる。
上記のケースではorder_dateを最初に評価して、これがNULLだったので次の" "が使用されただけである。

SELECT COALESCE(sales_date,shipped_date , order_date, now() ) FROM sales;

のようになっていた場合は、sales_dateがNULLならshipped_date、それもNULLならorder_date。それでもNULLなら現在の時刻、という具合にIF-ELSE構文のような挙動をする関数である、ということは知っておいて損はないだろう。実際の動きは下記のような感じ。

たまたま今回はNVLの代替として登場したが、使い方によっては上記のようなIF-ELSEを実現できるのである。
……もっとも、そんなに頻繁にNULL値に遭遇するようなデータ構造のデータベースとは、あまりお付き合いしたくないけども(^^;

おまけ

せっかくなので他のDBMSにおけるNVL(orその互換対応)について調べてみた。

DBMSごとのnull変換互換性
DBMS	機能	備考
Sybase	IsNull	IS
MS SQL Server	IsNull	IS:ま、元はSybaseだからね
MySQL	IfNull	ISじゃなくてIF
MS Access	Nz	MS SQLと互換性ありません
Oracle	NVL	本家？ COALESCEも持ってます
Informix	NVL	NVL派
PostgreSQL	COALESCE	本稿のメインｗ
DB2	COALESCE	型が違うとエラー
Firebird	COALESCE(Ver1.5+) iNVL,i64NVL,sNVL	Ver1.5以上で有効 1.0では拡張モジュールで型別NVLがある