PostgreSQL 7 min read

PostgreSQLにNVLは無い ― COALESCEで置き換えるときの型と空文字の注意

Oracle の NVL を PostgreSQL でそのまま使うと、関数が無いというエラーになります。MySQL の IFNULL もありません。どちらも SQL 標準の COALESCE に置き換えます。この記事の出力は PostgreSQL 17.6 で採りました。

SELECT nvl(price, 0) FROM t_nvl;
ERROR:  function nvl(integer, integer) does not exist
LINE 1: SELECT nvl(price, 0) FROM t_nvl;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

HINT には「型を明示的にキャストすれば」と出ますが、型の問題ではありません。

まず見る（結論）

  • NVL(a, b) は COALESCE(a, b) に書き換える。引数は3つ以上でもよい
  • 数値列の NULL を文字列で置き換えるなら COALESCE(price::text, 'なし')。キャストしないと型エラー
  • PostgreSQL では空文字 '' は NULL ではない。両方まとめて置き換えるなら COALESCE(NULLIF(memo, ''), 'なし')

COALESCE の使い方

例の表 t_nvl です。id=3 の memo は空文字、id=4 は NULL です。

 id | price |  memo
----+-------+--------
  1 |   100 | item A
  2 |       | item B
  3 |   300 |
  4 |       |
SELECT id, price, COALESCE(price, 0) AS price_coalesced FROM t_nvl ORDER BY id;
 id | price | price_coalesced
----+-------+-----------------
  1 |   100 |             100
  2 |       |               0
  3 |   300 |             300
  4 |       |               0
(4 rows)

COALESCE は引数を左から見て、最初の NULL でない値を返します。NVL と違って3つ以上渡せます。

SELECT COALESCE(NULL, NULL, 3);
 coalesce
----------
        3
(1 row)

数値列を文字列で置き換えると型エラー

SELECT COALESCE(price, 'なし') FROM t_nvl;
ERROR:  invalid input syntax for type integer: "なし"
LINE 1: SELECT COALESCE(price, 'なし') FROM t_nvl;
                               ^

COALESCE の戻り値は1つの型に決まります。この例では price の型 integer に合わせようとして、'なし' を整数に変換できずに失敗しています。列の方を文字列にしてから渡します。

SELECT id, price, COALESCE(price::text, 'なし') AS price_text FROM t_nvl ORDER BY id;
 id | price | price_text
----+-------+------------
  1 |   100 | 100
  2 |       | なし
  3 |   300 | 300
  4 |       | なし
(4 rows)

空文字は NULL ではない

Oracle では空文字 '' は NULL として扱われますが、PostgreSQL では別の値です。

SELECT '' IS NULL;     -- f
SELECT NULL IS NULL;   -- t

このため、COALESCE(memo, 'なし') では空文字の行は置き換わりません。

SELECT id, memo, COALESCE(memo, 'なし') AS memo_coalesced FROM t_nvl ORDER BY id;
 id |  memo  | memo_coalesced
----+--------+----------------
  1 | item A | item A
  2 | item B | item B
  3 |        |
  4 |        | なし
(4 rows)

空文字も「なし」にしたいときは、NULLIF(memo, '') で空文字を NULL に変えてから COALESCE に渡します。

SELECT id, memo, COALESCE(NULLIF(memo, ''), 'なし') AS memo2 FROM t_nvl ORDER BY id;
 id |  memo  | memo2
----+--------+-------
  1 | item A | item A
  2 | item B | item B
  3 |        | なし
  4 |        | なし
(4 rows)

Oracle から移したアプリケーションでは、空文字を入れたつもりの列が NULL になっていない（あるいはその逆の）ことがあります。WHERE memo IS NULL で探す処理は、移行後に件数が変わっていないか確かめます。

NVL をそのまま使いたいとき

Oracle 互換の関数を集めた拡張 orafce を入れると nvl が使えますが、PostgreSQL 本体には含まれていません。

COALESCE は Oracle・MySQL・Db2 でも同じ書き方で動くので、書き換えておけば後でデータベースを替えても書き直さずに済みます。

RELATED PostgreSQL