PHP小码农

Appearance

NVL / IFNULL 兼容 ​

从 Oracle 或 MySQL 迁移到 PostgreSQL 的开发者，第一件事往往就是找 NVL 和 IFNULL——因为在 Oracle 里 NVL(a, b) 和 MySQL 里 IFNULL(a, b) 太常见了。PostgreSQL 没有内置 NVL / IFNULL / ISNULL 这三个函数，直接调用会报 function does not exist。正确做法是改用标准的 COALESCE，或在确切需要保持旧代码兼容时，自建一层同义函数。本节讲清"为什么没有"、"怎么替代"、"如何安全地自建兼容函数"，以及自建函数必须注意的多态类型与易变性标记。

与「COALESCE / NULLIF」章节的分工

COALESCE / NULLIF 一节讲的是 COALESCE 本身的类型推导与短路语义；本节聚焦跨数据库兼容层——为什么 PostgreSQL 不提供 NVL / IFNULL、迁移时如何改写、以及自建兼容函数的正确写法与陷阱。

前置知识

阅读本节前，建议先了解：CASE WHEN（函数式用法）

基础概念 ​

概念术语说明
空值兜底NVLOracle 的函数，等价于 COALESCE(a, b)（两参数版）
空值兜底IFNULLMySQL / SQLite 的函数，同样等价于两参数 COALESCE
空值兜底ISNULLSQL Server / MySQL 的同义函数；PostgreSQL 里 IS NULL 是运算符不是函数
标准写法COALESCESQL 标准定义的唯一 NULL 兜底函数，PostgreSQL 原生支持
同义函数Alias / Shimming Function自建的、行为与旧数据库函数一致的包裹函数
多态类型Polymorphic Typeanyelement / anycompatible 等"占位类型"，让函数适配任意参数类型
兼容层Compatibility Layer为迁移期旧代码提供的一批同义函数（nvl / ifnull / isnull 等）
迁移期双写Dual Support同时保留新旧写法，逐步替换，避免一次性全量改动

为什么 PostgreSQL 不提供它们：NVL（Oracle）与 IFNULL（MySQL）的语义完全被 SQL 标准的 COALESCE 覆盖了，PostgreSQL 选择只实现标准函数，不提供厂商私有别名。这不是功能缺失，而是"不重复造语义相同的函数"的设计取向。

sql
-- 直接调用会报错
SELECT nvl(NULL, 1);
text
ERROR:  function nvl(unknown, integer) does not exist
LINE 1: SELECT nvl(NULL, 1);
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

同一件事用 COALESCE 写：

sql
SELECT
  coalesce(NULL, 1)         AS nvl_equivalent,     -- 1
  coalesce(NULL, NULL, 2)   AS multi_arg,          -- 2（NVL 只支持两参数，COALESCE 支持任意个）
  coalesce(1, 2)            AS not_null_wins;      -- 1
text
 nvl_equivalent | multi_arg | not_null_wins
----------------+-----------+--------------
              1 |         2 |             1
(1 row)

语法与用法 ​

如果你决定在数据库里保留旧函数名，最小做法是建三个 SQL 函数：

text
CREATE FUNCTION nvl(a anyelement, b anyelement) RETURNS anyelement;
CREATE FUNCTION ifnull(a anyelement, b anyelement) RETURNS anyelement;
CREATE FUNCTION isnull(a anyelement, b anyelement) RETURNS anyelement;

每个函数体都只有一行——调用 COALESCE：

sql
CREATE OR REPLACE FUNCTION nvl(a anyelement, b anyelement)
RETURNS anyelement
LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce($1, $2);
$$;
text
CREATE FUNCTION

用起来和旧数据库一样：

sql
SELECT
  nvl(NULL, 'default')   AS text_fallback,   -- default
  nvl('abc', 'default')  AS text_first,      -- abc
  nvl(NULL, 42)          AS int_fallback;    -- 42
text
 text_fallback | text_first | int_fallback
---------------+------------+-------------
 default       | abc        |          42
(1 row)

别真的长期依赖同义函数

自建 nvl 只是迁移期的过渡手段。长期看，COALESCE 更标准、可读性更好、也不会与"某个同事以为这是内置函数"产生认知分歧。条件允许时应把旧写法全量替换为 coalesce 后删掉这些函数。

详细说明 ​

三个函数在各数据库中的对应关系 ​

先建立一张完整的对照表，迁移时按表改写：

写法出现的数据库PostgreSQL 是否内置PostgreSQL 等价写法
COALESCE(a, b, ...)全部（SQL 标准）✅ 是原样使用
NVL(a, b)Oracle❌ 否coalesce(a, b)
NVL2(a, b, c)Oracle❌ 否CASE WHEN a IS NOT NULL THEN b ELSE c END
IFNULL(a, b)MySQL / SQLite❌ 否coalesce(a, b)
ISNULL(a, b)SQL Server / MySQL❌ 否coalesce(a, b)
IF(a, b, c)MySQL❌ 否CASE WHEN a THEN b ELSE c END
DECODE(a, b, c, d)Oracle❌ 否CASE a WHEN b THEN c ELSE d END

注意 NVL2 与 IF 不只是"换个名字"——它们是三参数的条件函数，语义比 COALESCE 多一层，不能简单用 coalesce 替换（下一小节给出改写方式）。

NVL 到 COALESCE 的逐条改写 ​

两参数 NVL(a, b) → coalesce(a, b)，一一对应：

sql
-- Oracle 写法
-- SELECT nvl(phone, '无') FROM users;
-- PostgreSQL 写法
SELECT coalesce(phone, '无') AS phone_display FROM users;

NVL 的嵌套 → COALESCE 的多参数，这是改写中最能"变简洁"的部分：

sql
-- Oracle：三层嵌套，越写越长
-- SELECT nvl(nvl(nickname, username), '匿名') FROM users;
-- PostgreSQL：一次列全
SELECT coalesce(nickname, username, '匿名') AS display_name FROM users;

NVL2(a, b, c) → CASE WHEN，因为它不是"兜底"而是"分支"：

sql
-- Oracle
-- SELECT nvl2(phone, '有电话', '无电话') FROM users;
-- PostgreSQL：注意 NULL 判断必须用搜索 CASE
SELECT CASE WHEN phone IS NOT NULL THEN '有电话' ELSE '无电话' END AS phone_state
FROM users;

什么时候 NVL2 也能用 COALESCE

当 b 是常量而 c 恰好是 NULL 时，NVL2(a, b, NULL) 确实等价于 coalesce(a, b)。但只要 b / c 都是非 NULL 值，就必须用 CASE。

ISNULL(a, b) 的双重身份要小心：在 SQL Server / MySQL 里它是"兜底函数"，但在 PostgreSQL 里 ISNULL 若存在于某些扩展中语义可能不同；而 IS NULL 是标准的空值判断运算符，与函数完全是两回事，不要混写：

sql
-- 正确：这是运算符
SELECT * FROM users WHERE phone IS NULL;

-- 错误：不能写成函数
SELECT * FROM users WHERE isnull(phone);

自建兼容函数：多态类型 anyelement ​

nvl 要同时支持 text、integer、date 等类型，就必须用多态类型（polymorphic type）而不是写死 text：

sql
-- 错误：写死类型，只能用于 text
CREATE FUNCTION nvl(a text, b text) RETURNS text ...;
-- SELECT nvl(NULL, 42);  → 报错，42 不是 text
sql
-- 正确：任一元素类型（anyelement）
CREATE OR REPLACE FUNCTION nvl(a anyelement, b anyelement)
RETURNS anyelement
LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce($1, $2);
$$;

anyelement 的作用是：两个参数与返回值绑定到同一个具体类型，由调用时的实参决定。这与 COALESCE 的类型归约规则天然一致（coalesce(1, 2.5) 归约为 numeric）：

写法类型选择说明
anyelement两个参数必须完全同类型，返回同类型严格模式
anycompatible允许归约到公共类型PostgreSQL 13+，更接近 COALESCE

anyelement 是严格同类型，所以 nvl(NULL, 42) 能工作（NULL 被推断为 integer），但 nvl(1, 2.5) 会报"参数类型不一致"：

sql
SELECT nvl(1, 2.5);
text
ERROR:  function nvl(integer, numeric) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

如果想让它像 COALESCE 一样宽容，用 anycompatible（PostgreSQL 13+）：

sql
CREATE OR REPLACE FUNCTION nvl(a anycompatible, b anycompatible)
RETURNS anycompatible
LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce($1, $2);
$$;

SELECT nvl(1, 2.5) AS mixed, pg_typeof(nvl(1, 2.5)) AS result_type;   -- 1 | numeric

同名的两个重载会冲突

不能同时保留 nvl(anyelement, anyelement) 与 nvl(anycompatible, anycompatible)——它们对同一组实参会同时匹配，造成歧义。只建一个，把旧的删掉再建新的：

sql
DROP FUNCTION IF EXISTS nvl(anyelement, anyelement);
DROP FUNCTION IF EXISTS nvl(anycompatible, anycompatible);

三个兼容函数的完整 DDL ​

一次性建好迁移期需要的三个函数，使用 CREATE OR REPLACE 保证幂等：

sql
-- COALESCE 的两参数别名
CREATE OR REPLACE FUNCTION nvl(a anycompatible, b anycompatible)
RETURNS anycompatible
LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce($1, $2);
$$;

CREATE OR REPLACE FUNCTION ifnull(a anycompatible, b anycompatible)
RETURNS anycompatible
LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce($1, $2);
$$;

CREATE OR REPLACE FUNCTION isnull_fn(a anycompatible, b anycompatible)
RETURNS anycompatible
LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce($1, $2);
$$;
text
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION

不要覆盖内置函数名

isnull 在部分 PostgreSQL 生态里可能已被占用（如某些兼容扩展）。若要建 isnull，先确认命名空间里没有同名函数：

sql
-- 先查是否已存在同名函数
SELECT p.oid::regprocedure AS signature, p.provolatile
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname IN ('nvl', 'ifnull', 'isnull')
  AND n.nspname = current_schema();

若发现冲突，改用 isnull_fn 之类的名字，避免遮蔽系统行为。

用视图屏蔽函数名 ​

不改动表结构的另一种兼容手段是建视图：把 nvl 的调用固化在视图定义里，应用层查视图而非直接查表。这样应用代码完全不需要改，迁移风险最小：

sql
CREATE OR REPLACE VIEW v_users_display AS
SELECT
  id,
  coalesce(nullif(trim(nickname), ''), nullif(trim(username), ''), '匿名用户') AS display_name,
  coalesce(phone, '未填写')                                                     AS phone_display
FROM users;
sql
SELECT id, display_name, phone_display FROM v_users_display ORDER BY id;

视图 vs 函数，怎么选

手段适用场景代价
改写成 coalesce首选，改动点在 SQL 里需要改应用代码
自建同义函数SQL 分散难改、需要最小改动多一层函数调用，易被误认为内置
建兼容视图应用层只读、不想动任何查询只适用于读场景，写入仍需改

函数易变性标记的选择 ​

三个兼容函数都应标记为 IMMUTABLE——coalesce 只依赖参数，同样的输入永远得到同样的输出。这个标记有三个实际好处：

  1. 可被常量折叠：优化器能在计划阶段直接算出结果；
  2. 可用于表达式索引与生成列；
  3. 可用于 CHECK 约束。
sql
-- 因标记为 IMMUTABLE，可用于表达式索引
CREATE UNIQUE INDEX uq_users_phone_eff ON users (nvl(nullif(trim(phone), ''), ''));
sql
-- 验证标记确实生效
SELECT proname, provolatile
FROM pg_proc
WHERE proname IN ('nvl', 'ifnull', 'isnull_fn')
  AND pronamespace = current_schema()::regnamespace;
text
 proname  | provolatile
----------+-------------
 ifnull   | i
 isnull_fn| i
 nvl      | i
(3 rows)

provolatile = 'i' 即 IMMUTABLE。

标记 IMMUTABLE 的前提

只有当函数体只调用 IMMUTABLE 函数时才成立。若把 nvl 写成 coalesce($1, now()) 之类含 STABLE 调用的形式，标记成 IMMUTABLE 就是谎报，会导致索引/生成列数据不一致。本节函数体只有 coalesce($1, $2)，可以放心标记。

兼容函数的性能代价 ​

一层 SQL 函数包裹是否会被优化器内联？答案是通常会被内联——PostgreSQL 对简单的 LANGUAGE sql 函数有内联优化，函数调用会被展开为直接的 coalesce 表达式，不产生额外开销：

sql
EXPLAIN (COSTS OFF, VERBOSE)
SELECT id FROM users WHERE nvl(phone, 'x') = 'x';
text
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on public.users
   Output: id
   Filter: (COALESCE(phone, 'x'::text) = 'x'::text)
(3 rows)

计划里出现的是 COALESCE(...) 而非 nvl(...)——证明函数已被内联，没有额外调用开销。

内联不是无条件的

函数被内联需要满足若干条件（函数体是单个 SELECT、无易变函数、无 SET 子句等）。一旦函数体变复杂，内联可能失效，此时每行都会产生一次函数调用。因此兼容函数应保持极简，只做一层转发。

实战示例 ​

场景：从 Oracle 迁移到 PostgreSQL，把旧查询改写并建立兼容层。

步骤 1：建表并造数据

sql
DROP TABLE IF EXISTS users;
DROP FUNCTION IF EXISTS nvl(anycompatible, anycompatible);
DROP FUNCTION IF EXISTS ifnull(anycompatible, anycompatible);

CREATE TABLE users (
  id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username text,
  nickname text,
  phone    text,
  balance  numeric(10,2)
);

INSERT INTO users (username, nickname, phone, balance) VALUES
  ('zhangsan', '张三',   '13800000001', 100.50),
  ('lisi',     NULL,     NULL,          NULL),
  ('   ',      '   ',    '',            0.00),
  (NULL,       NULL,     NULL,          NULL);
text
CREATE TABLE
INSERT 0 4

步骤 2：复现"函数不存在"的错误

sql
-- 直接使用 Oracle / MySQL 写法
SELECT nvl(phone, '无') FROM users;
text
ERROR:  function nvl(unknown, text) does not exist
LINE 1: SELECT nvl(phone, '无') FROM users;
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

步骤 3：建立兼容函数

sql
CREATE OR REPLACE FUNCTION nvl(a anycompatible, b anycompatible)
RETURNS anycompatible
LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce($1, $2);
$$;

CREATE OR REPLACE FUNCTION ifnull(a anycompatible, b anycompatible)
RETURNS anycompatible
LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce($1, $2);
$$;
text
CREATE FUNCTION
CREATE FUNCTION

步骤 4：兼容写法与标准写法结果一致

sql
SELECT
  id,
  nvl(phone, '无')      AS via_nvl,
  ifnull(phone, '无')   AS via_ifnull,
  coalesce(phone, '无') AS via_coalesce
FROM users
ORDER BY id;
text
 id | via_nvl | via_ifnull | via_coalesce
----+---------+------------+--------------
  1 | 13800000001 | 13800000001 | 13800000001
  2 | 无      | 无         | 无
  3 |         |            |
  4 | 无      | 无         | 无
(4 rows)

第 3 行的 phone 是空串 ''，COALESCE 认为空串不是 NULL，因此三个函数都原样返回空串——这正是空串必须先用 nullif(trim(...), '') 归一的原因：

sql
SELECT
  id,
  coalesce(nullif(trim(phone), ''), '无')    AS clean_phone,
  nvl(nullif(trim(phone), ''), '无')         AS compat_clean_phone
FROM users
ORDER BY id;
text
 id | clean_phone | compat_clean_phone
----+-------------+--------------------
  1 | 13800000001 | 13800000001
  2 | 无          | 无
  3 | 无          | 无
  4 | 无          | 无
(4 rows)

步骤 5：验证多态类型与类型归约

sql
SELECT
  nvl(NULL, 42)                       AS int_fallback,
  pg_typeof(nvl(NULL, 42))            AS int_type,
  nvl(NULL, 2.5)                      AS numeric_fallback,
  pg_typeof(nvl(NULL, 2.5))           AS numeric_type,
  nvl(1, 2.5)                         AS unified,
  pg_typeof(nvl(1, 2.5))              AS unified_type;
text
 int_fallback | int_type | numeric_fallback | numeric_type | unified | unified_type
--------------+----------+------------------+--------------+---------+--------------
           42 | integer  |              2.5 | numeric      |       1 | numeric
(1 row)

nvl(1, 2.5) 返回 numeric，与 coalesce(1, 2.5) 的类型归约完全一致——这是 anycompatible 在起作用。

步骤 6：内联验证

sql
ANALYZE users;
EXPLAIN (COSTS OFF, VERBOSE)
SELECT id FROM users WHERE nvl(phone, 'x') = 'x';
text
                      QUERY PLAN
--------------------------------------------------------
 Seq Scan on public.users
   Output: id
   Filter: (COALESCE(phone, 'x'::text) = 'x'::text)
(3 rows)

计划中是 COALESCE，证明 SQL 函数已被内联，没有额外开销。

步骤 7：清理（迁移完成后应删掉兼容函数）

sql
DROP FUNCTION IF EXISTS nvl(anycompatible, anycompatible);
DROP FUNCTION IF EXISTS ifnull(anycompatible, anycompatible);
DROP TABLE users;

注意事项 ​

  • PostgreSQL 没有内置 NVL / IFNULL / ISNULL 函数，直接调用报 function does not exist。正确替代是 COALESCE：
sql
-- 错误！
SELECT nvl(phone, '无') FROM users;
-- 正确
SELECT coalesce(phone, '无') FROM users;
  • NVL2(a, b, c) 不能简单换成 coalesce，它是三参数分支语义，必须用 CASE WHEN：
sql
CASE WHEN a IS NOT NULL THEN b ELSE c END

  • IS NULL 是运算符，不是函数，不要写成 isnull(col) 来判空。

  • 自建函数必须用多态类型，写死 text 只能处理字符串：

sql
-- 错误！只能用于 text
CREATE FUNCTION nvl(a text, b text) RETURNS text ...;
-- 正确
CREATE FUNCTION nvl(a anyelement, b anyelement) RETURNS anyelement ...;

  • anyelement 要求两参数同类型；要允许类型归约（如 nvl(1, 2.5)）需用 anycompatible（PostgreSQL 13+）。

  • anyelement 与 anycompatible 版本的同名函数不能共存，会造成重载歧义，必须删掉旧的再建。

  • COALESCE 只替换 NULL，不替换空串，nvl(phone, '无') 对 phone = '' 无效，需先 nullif(trim(...), '')。

  • 兼容函数要标记 IMMUTABLE，但前提是函数体只调用不可变函数；把 now() 之类包进去再标 IMMUTABLE 会导致索引/生成列数据不一致。

  • 兼容函数必须保持极简才能被优化器内联；复杂函数体会退化为逐行函数调用，带来性能损耗。

  • 不要长期依赖同义函数。它们会让后续接手的人误以为是内置函数，应作为迁移期过渡手段，改完即删。

最佳实践 ​

  1. 迁移的首选方案是把 nvl / ifnull 直接改写成 coalesce，而不是建兼容函数：

    sql
    -- Oracle / MySQL
nvl(phone, '无')  /  ifnull(phone, '无')
-- PostgreSQL 标准
coalesce(phone, '无')

  2. 多参数嵌套的 NVL 改写成单次 COALESCE，代码更短更清晰：

    sql
    -- 旧写法
nvl(nvl(nickname, username), '匿名')
-- 新写法
coalesce(nickname, username, '匿名')

  3. 确实需要兼容层时，用一次性的迁移脚本建函数，并在脚本里显式删除，避免残留：

    sql
    CREATE OR REPLACE FUNCTION nvl(a anycompatible, b anycompatible)
RETURNS anycompatible LANGUAGE sql IMMUTABLE AS $$ SELECT coalesce($1, $2); $$;

  4. 兼容函数一律用 anycompatible（PostgreSQL 13+），语义最接近 COALESCE，减少类型报错。

  5. 优先用视图而不是函数做兼容，读场景下应用代码可以完全不动，风险最低。

  6. 无论用哪种方案，都别忘空串归一。真正的清洗链是 coalesce(nullif(trim(x), ''), '默认')，与函数名无关。

  7. 兼容函数建好后立即验证内联，EXPLAIN (VERBOSE) 应显示展开后的表达式：

    sql
    EXPLAIN (COSTS OFF, VERBOSE) SELECT nvl(col, 'x') FROM t;

  8. 把"删掉兼容函数"写进迁移 checklist，并在删除前用 pg_depend 确认没有任何对象还在依赖它们：

    sql
    SELECT dependent.relname
FROM pg_depend d
JOIN pg_proc p ON p.oid = d.refobjid
JOIN pg_class dependent ON dependent.oid = d.objid
WHERE p.proname IN ('nvl', 'ifnull');

下一节

继续学习：ARRAY 构造函数与操作

参考链接 ​