PHP小码农

Appearance

COALESCE / NULLIF ​

COALESCE 与 NULLIF 是 PostgreSQL 中仅有的两个直接处理 NULL 语义的条件函数：前者从参数列表里挑出第一个非 NULL 的值，后者把一个"等于特定值"的输入转换成 NULL。它们不是普通的 C 函数，而是由解析器识别、由优化器直接处理的条件表达式节点——这个身份决定了它们的类型推导规则、短路行为与索引可用性都与众不同。本节从函数视角讲解它们的定义、类型规则，以及在 DDL、默认值、UPSERT 回填与聚合中的实际用法。

与「COALESCE / NULLIF / GREATEST / LEAST」运算符章节的分工

COALESCE / NULLIF / GREATEST / LEAST 一章从表达式写法出发，重点讲"怎么写、什么时候用"；本节从函数视角出发，重点讲参数个数与类型推导规则、短路求值的边界、以及在 DEFAULT / CHECK / ON CONFLICT / 表达式索引中的落地方式。两节互补，避免只知语法不知边界。

前置知识

阅读本节前，建议先了解：ORDER BY 在聚合中

基础概念 ​

概念术语说明
空值兜底COALESCE返回参数列表中第一个非 NULL 的参数
相等转空NULLIF两个参数相等时返回 NULL，否则返回第一个参数
条件表达式节点Conditional ExpressionCOALESCE / NULLIF 在解析树中是特殊节点，不是 pg_proc 里的普通函数
类型归约Type Unification所有参数先被推导到一个共同类型，返回值是该类型
短路求值Short-Circuit一旦结果确定，后续参数不再求值
未知类型字面量Unknown Literal'abc' 这类未加类型标注的字符串常量，参与类型归约
值域默认化Defaulting用 COALESCE 把 NULL 变成业务默认值，避免下游判空
无效值归一Sentinel-to-NULL用 NULLIF 把 0、空串等哨兵值归一为 NULL

两者的签名（pg_proc 中并不存在 coalesce 这一行，下面是语义签名）：

表达式语义签名返回类型
COALESCEcoalesce(v1, v2, ...)所有参数的公共类型
NULLIFnullif(v1, v2)第一个参数 v1 的类型

最小示例：

sql
SELECT
  coalesce(NULL, NULL, '兜底')  AS pick_third,   -- 兜底
  coalesce(NULL, 'B', 'C')      AS pick_second,  -- B
  nullif(10, 10)                AS equal_to_null,-- NULL
  nullif(10, 3)                 AS not_equal;    -- 10
text
 pick_third | pick_second | equal_to_null | not_equal
------------+-------------+---------------+-----------
 兜底       | B           |               |        10
(1 row)

语法与用法 ​

text
COALESCE ( 值1 [, 值2, ...] )
NULLIF ( 值1, 值2 )

两条必须记住的硬规则：

  1. COALESCE 的参数个数不限，但 SQL 标准要求至少两个（PostgreSQL 实现上允许更少，但单参数没有任何意义）；
  2. NULLIF 恰好两个参数，需要排除多个无效值时只能嵌套 NULLIF。
sql
-- 标准形态
SELECT
  coalesce(a, b, c, d, '默认') AS first_non_null,
  nullif(status, 'unknown')    AS normalized_status
FROM t;

最小可运行示例，覆盖两个函数的返回值边界：

sql
SELECT
  coalesce(NULL)                  AS only_null,      -- NULL（全为 NULL 则返回 NULL）
  coalesce(NULL, 0)               AS zero_is_value,  -- 0（0 不是 NULL）
  nullif(NULL, 'x')               AS null_v1,        -- NULL（第一个参数原样返回）
  nullif('x', NULL)               AS null_v2;        -- x（NULL 与任何值都不相等）
text
 only_null | zero_is_value | null_v1 | null_v2
-----------+---------------+---------+---------
           |             0 |         | x
(1 row)

详细说明 ​

COALESCE 的求值模型 ​

COALESCE 从左到右扫描参数列表，返回第一个"不是 SQL NULL"的值；如果全部为 NULL，结果是 NULL。它不关心值的内容，只判断"是否为 NULL"：

sql
SELECT
  coalesce(''::text, '兜底')     AS empty_string_is_value,  -- 空串（不是 NULL！）
  coalesce(0, 100)               AS zero_is_value,          -- 0
  coalesce(false, true)          AS false_is_value,         -- f
  coalesce(NULL, NULL, NULL)     AS all_null;               -- NULL
text
 empty_string_is_value | zero_is_value | false_is_value | all_null
-----------------------+---------------+----------------+----------
                       |             0 | f              |
(1 row)

这条性质决定了 COALESCE 不能替代业务条件判断：它只会替换 NULL，不会替换空串、0、false 这些"业务上的无效值"。要处理空串必须先 NULLIF 归一，这也正是 coalesce(nullif(trim(x), ''), '默认') 这个固定组合存在的原因。

类型推导规则 ​

这是 COALESCE 最容易出错的部分。规则可以概括为三步：

text
1. 收集所有参数的类型；
2. 若存在「unknown」类型（无标注的字符串字面量），暂不作为候选；
3. 在已知类型中选出公共类型（同类型类别内按类别偏好），
   把其余参数与 unknown 字面量统一转换到该类型；
   若无法归约到同一类型 → 报错。
参数组合推导结果说明
coalesce(NULL, 1, 2.5)numericint4 与 numeric 归约到 numeric
coalesce(1, 2)integer同类型，保持 integer
coalesce(1::smallint, 2)integer归约到更宽的 integer
coalesce(NULL, 'abc')text唯一候选是 unknown，落到 text
coalesce(1, 'abc')报错'abc' 被解析为 integer 时失败
coalesce(1, true)报错跨类型类别，无法归约

验证跨类型归约确实发生：

sql
SELECT
  coalesce(NULL, 1, 2.5)                 AS value,
  pg_typeof(coalesce(NULL, 1, 2.5))      AS value_type,
  pg_typeof(coalesce(1, 2))              AS int_type,
  pg_typeof(coalesce(NULL, 'abc'))       AS text_type;
text
 value | value_type | int_type | text_type
-------+------------+----------+-----------
     1 | numeric    | integer  | text
(1 row)

注意结果值显示为 1 而不是 1.0，但它的类型已经是 numeric——这正是"先归约类型、再取值"的证据。

跨类型类别会直接报错，且错误信息明确指出了冲突的两侧：

sql
-- 错误！integer 与 boolean 属于不同类别，无法归约
SELECT coalesce(1, true);
text
ERROR:  COALESCE types integer and boolean cannot be matched
LINE 1: SELECT coalesce(1, true);
sql
-- 错误！'abc' 是 unknown 字面量，被归约到 integer 后无法转换
SELECT coalesce(1, 'abc');
text
ERROR:  invalid input syntax for type integer: "abc"
LINE 1: SELECT coalesce(1, 'abc');

规避办法是显式转型，而不是靠隐式规则去猜：

sql
-- 正确：显式把冲突一侧转型，或把字面量标注为目标类型
SELECT coalesce(1::text, 'abc')     AS to_text,     -- 1
       coalesce(1, '2'::integer)    AS to_int;      -- 1

短路求值 ​

COALESCE 只求值"确定结果所必需"的参数——一旦某个参数非 NULL，它右边的参数完全不会被计算。这与 CASE 的行为一致，且是有文档保证的语义，可以放心依赖：

sql
-- 安全：denominator 非 0 时，右侧的除法根本不会执行
SELECT coalesce(nullif(20, 0), 20 / 0) AS safe;   -- 20，不会报 division by zero
text
 safe
------
   20
(1 row)

反过来，这个特性也能用来规避昂贵计算或易错表达式：

sql
-- 若 cache_value 已有值，昂贵的子查询不会执行
SELECT coalesce(
         cache_value,
         (SELECT max(score) FROM huge_table)   -- 只在 cache_value 为 NULL 时求值
       ) AS score
FROM score_cache
WHERE id = 1;

短路 ≠ 不解析

短路只发生在执行期。参数的语法与类型仍会全部参与解析与类型推导——coalesce(1, true) 依然报类型错误，coalesce(1, 1/0) 也不会因为"根本没用到"而豁免类型检查。

NULLIF 的值语义与返回类型 ​

NULLIF(v1, v2) 用 = 比较两个参数：相等返回 NULL，不等返回 v1。两个必须记住的性质：

  1. 返回类型是 v1 的类型，不会因为比较而改变：
    sql
    SELECT pg_typeof(nullif(1, 2))            AS t1,   -- integer
       pg_typeof(nullif(1.5, 2))          AS t2,   -- numeric
       pg_typeof(nullif('a'::text, 'b'))  AS t3;   -- text
  2. 两个参数必须可比较，且类型要能归约：
sql
-- 错误！无法归约到同一类型
SELECT nullif(1, true);
text
ERROR:  NULLIF types integer and boolean cannot be matched
sql
-- 错误！'x' 被归约为 integer 后转换失败
SELECT nullif(1, 'x');
text
ERROR:  invalid input syntax for type integer: "x"

还有一个容易被忽略的语义细节：NULL = NULL 的结果是 NULL，而不是 true，所以当 v1 为 NULL 时 NULLIF 原样返回 NULL（结果相同）；但当 v2 为 NULL 时永远不会相等，v1 原样返回：

sql
SELECT
  nullif(NULL, 1)   AS v1_is_null,   -- NULL
  nullif('a', NULL) AS v2_is_null;   -- a
text
 v1_is_null | v2_is_null
------------+------------
            | a
(1 row)

NULLIF 防除零的标准范式 ​

a / NULLIF(b, 0) 是 SQL 世界里"防止 division by zero"的固定写法：当 b 为 0 时，NULLIF(b, 0) 返回 NULL，NULL 参与任何算术运算仍得 NULL，因此不会触发除零错误，只是结果变成 NULL。

sql
-- 危险！b 为 0 时直接报错
SELECT 100 / 0;
text
ERROR:  division by zero
sql
-- 安全：分母为 0 时结果整体为 NULL，由调用方决定如何兜底
SELECT 100 / NULLIF(0, 0) AS result;   -- NULL

实际报表中，通常还要再用 COALESCE 把 NULL 兜成 0，形成 coalesce(a / NULLIF(b, 0), 0) 两级写法：

sql
SELECT
  campaign,
  clicks,
  orders,
  coalesce(round(orders::numeric / NULLIF(clicks, 0), 4), 0) AS conversion_rate
FROM campaigns
ORDER BY id;
写法b = 0 时b IS NULL 时结论
a / b报错NULL不可用
a / coalesce(b, 1)仍报错，且结果错NULL错误用法
a / NULLIF(b, 0)NULLNULL正确
coalesce(a / NULLIF(b, 0), 0)00最常用于报表

COALESCE 不能防除零

coalesce(b, 0) 只是"若 b 为 NULL 就用 0"，它恰恰可能制造一个 0 作为分母：

sql
-- 错误！b 为 NULL 时变成 0，随后除零报错
SELECT a / coalesce(b, 0) FROM t;
-- 正确
SELECT a / NULLIF(b, 0) FROM t;

NULLIF 做「空字符串转 NULL」 ​

应用层写入的 ''（空串）与"未填写"（NULL）语义上常常是一回事，但数据库里是两个值。数据清洗的第一件事就是统一成一种——nullif(trim(x), '') 就是干这个的：

sql
SELECT
  nullif(trim('   '), '')       AS blank_to_null,    -- NULL
  nullif(trim('  ab  '), '')    AS trimmed,          -- ab
  nullif(trim(''), '')          AS empty_to_null;    -- NULL
text
 blank_to_null | trimmed | empty_to_null
---------------+---------+---------------
               | ab      |
(1 row)

为什么必须配 trim：' '、'\t' 这类"看起来是空"的值不写 trim 就会被 nullif 判为有效值：

sql
-- 不完整：只有严格空串会被归一，含空白的字符串逃逸
SELECT nullif('   ', '') AS still_blank;   -- '   '（不是 NULL）

配 coalesce 后形成完整的清洗链，这是数据迁移与 ETL 里的高频组合：

sql
SELECT
  id,
  coalesce(nullif(trim(nickname), ''), nullif(trim(username), ''), '匿名用户') AS display_name
FROM users
ORDER BY id;

在列默认值中的用法 ​

DEFAULT 表达式不能引用其他列，但可以调用稳定函数（如 now()、current_setting）。用 COALESCE + NULLIF 可以把"未配置则回落"的逻辑直接固化到默认值里：

sql
CREATE TABLE audit_log (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant     text        NOT NULL
               DEFAULT coalesce(nullif(current_setting('app.tenant', true), ''), 'public'),
  action     text        NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
text
CREATE TABLE

current_setting('app.tenant', true) 在参数未设置时返回 NULL（第二个参数 missing_ok = true 的作用），nullif(..., '') 再把"显式设为空串"也归一为 NULL，最后由 coalesce 落到 'public'。三层合起来才是完整的兜底链。

在 INSERT 回填与 UPSERT 中的用法 ​

COALESCE 在 UPSERT 的部分列更新中极其常用：用 excluded.col 与旧值 t.col 比较，只覆盖"新值里真正带了的列"，避免把未提供的列清成 NULL。

sql
INSERT INTO users (id, nickname, phone, email)
VALUES (1, '新昵称', NULL, 'new@example.com')
ON CONFLICT (id) DO UPDATE
SET
  nickname = coalesce(excluded.nickname, users.nickname),  -- 新值为 NULL 则保留旧值
  phone    = coalesce(excluded.phone,    users.phone),
  email    = coalesce(excluded.email,    users.email);

如果希望"空串也视为未更新"，把 coalesce 换成本节的清洗链即可：

sql
INSERT INTO users (id, nickname) VALUES (1, '   ')
ON CONFLICT (id) DO UPDATE
SET nickname = coalesce(nullif(trim(excluded.nickname), ''), users.nickname);

MERGE 中的同类用法

在 MERGE ... WHEN MATCHED THEN UPDATE SET 里，同样可以用 coalesce(source.col, target.col) 表达"源缺省则保留目标值"。关于 MERGE 的完整语义，参见 MERGE。

与聚合函数组合 ​

聚合函数遇到空集时的行为不一致，COALESCE 负责抹平差异：

聚合函数空集返回值是否需要 COALESCE
sumNULL需要
avg / max / minNULL需要
count(*) / count(col)0不需要（天然返回 0）
array_aggNULL需要，兜底为空数组 '{}'
string_aggNULL需要，兜底为空串
sql
-- 正确：sum 在无匹配行时返回 NULL，需要兜底为 0
SELECT coalesce(sum(amount), 0) AS total_amount
FROM orders WHERE status = 'nonexistent';
text
 total_amount
--------------
         0.00
(1 row)
sql
-- 多余：count 永不返回 NULL，coalesce 是无效包装
SELECT coalesce(count(*), 0) AS cnt FROM orders;   -- 正确但没必要

数组与字符串聚合的兜底则很有必要，因为下游多数代码不接受 NULL：

sql
SELECT
  u.id,
  coalesce(array_agg(o.order_no ORDER BY o.created_at), '{}'::text[]) AS order_nos,
  coalesce(string_agg(o.order_no, ',' ORDER BY o.created_at), '')    AS order_csv
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id
ORDER BY u.id;

聚合内部的 ORDER BY 保证元素顺序稳定（详见 ORDER BY 在聚合中），COALESCE 保证空集也能得到合法值——两者是搭配使用的关系。

在表达式索引中的可用性 ​

COALESCE 与 NULLIF 的求值只依赖参数，不依赖会话状态或随机数，因此在参数都是不可变表达式时，它们可以出现在表达式索引里。这是把「兜底后的值」当作检索键的关键手段：

sql
-- 场景一：把 NULL 归一为 0 之后建索引，让条件能走索引
CREATE INDEX idx_products_price_eff ON products ((coalesce(price, 0)));

-- 查询必须写得与索引表达式完全一致，否则用不上索引
EXPLAIN (COSTS OFF)
SELECT id FROM products WHERE coalesce(price, 0) = 0;
text
                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_products_price_eff on products
   Index Cond: (COALESCE(price, '0'::numeric) = '0'::numeric)
(2 rows)
sql
-- 场景二：把空串归一为 NULL，实现「有效值唯一」的约束
CREATE UNIQUE INDEX uq_users_email_valid ON users ((nullif(lower(trim(email)), '')));

易变输入会让索引定义被拒绝

索引表达式必须满足 IMMUTABLE。如果 COALESCE 的参数里出现 now()、random()、current_user 这类 STABLE/VOLATILE 函数，CREATE INDEX 会直接报 "functions in index expression must be marked IMMUTABLE"。判断标准是参数，不是 coalesce 本身。

反过来，也要注意在 WHERE 里包裹索引列会让普通索引失效：

sql
-- 不推荐：普通 btree 索引无法用于 coalesce(price, 0) > 100
SELECT id FROM products WHERE coalesce(price, 0) > 100;

-- 推荐：拆成等价条件，让两个分支各自走索引
SELECT id FROM products WHERE price > 100 OR (price IS NULL AND 0 > 100);

与 GREATEST / LEAST 的混淆点预告 ​

这三个函数经常被放在一起记，但语义完全不同，混淆会写出静默错误的结果：

函数判断依据greatest(1, NULL) 的结果
COALESCE是否为 NULL—（参数是值列表，不是比较）
GREATEST值的大小比较NULL（任一参数为 NULL 即传染）

最容易踩的坑是把 coalesce 当成"取最大值"：

sql
-- 错误理解：以为会返回 100
SELECT coalesce(0, 100);   -- 0，因为 0 不是 NULL

-- 想要取最大值应该用
SELECT greatest(0, 100);   -- 100

GREATEST / LEAST 的 NULL 传播规则与替代写法，将在下一节展开。

实战示例 ​

场景：用户资料清洗 + 转化率报表 + 增量回填。

步骤 1：建表并造数据

sql
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS campaigns;
DROP TABLE IF EXISTS products;

CREATE TABLE users (
  id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username text,
  nickname text,
  phone    text,
  email    text
);

CREATE TABLE campaigns (
  id      integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    text    NOT NULL,
  clicks  integer NOT NULL DEFAULT 0,
  orders  integer NOT NULL DEFAULT 0
);

CREATE TABLE products (
  id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku   text NOT NULL UNIQUE,
  price numeric(10,2)
);

INSERT INTO users (username, nickname, phone, email) VALUES
  ('zhangsan', '张三',   '13800000001', 'zs@example.com'),
  ('lisi',     NULL,     NULL,          'ls@example.com'),
  ('   ',      '   ',    NULL,          '   '),
  (NULL,       NULL,     NULL,          NULL);

INSERT INTO campaigns (name, clicks, orders) VALUES
  ('春季促销', 1200, 36),
  ('新品首发',    0,  0),
  ('会员日',     800, 12);

INSERT INTO products (sku, price) VALUES
  ('SKU-1001', 1299.00),
  ('SKU-1002', NULL);
text
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 4
INSERT 0 3
INSERT 0 2

步骤 2：多级兜底 + 空串归一

sql
SELECT
  id,
  coalesce(nullif(trim(nickname), ''), nullif(trim(username), ''), '匿名用户') AS display_name,
  coalesce(nullif(trim(email), ''), nullif(trim(phone), ''), '无联系方式')     AS contact
FROM users
ORDER BY id;
text
 id | display_name |    contact
----+--------------+----------------
  1 | 张三          | zs@example.com
  2 | lisi          | ls@example.com
  3 | 匿名用户      | 无联系方式
  4 | 匿名用户      | 无联系方式
(4 rows)

第 3 行是关键：' ' 被 trim + nullif 归一为 NULL，因此落到了兜底值，而不是原样输出三个空格。

步骤 3：验证短路求值确实生效

sql
-- 若第一个参数非 0，右侧的 20 / 0 完全不求值
SELECT
  coalesce(nullif(20, 0), 20 / 0)   AS short_circuit_hit,    -- 20
  coalesce(nullif(0, 0), 20 / 20)   AS short_circuit_miss;   -- 1
text
 short_circuit_hit | short_circuit_miss
-------------------+-------------------
                20 |                 1
(1 row)

步骤 4：转化率报表（两级兜底）

sql
SELECT
  name,
  clicks,
  orders,
  round(orders::numeric / NULLIF(clicks, 0), 4)              AS raw_rate,
  coalesce(round(orders::numeric / NULLIF(clicks, 0), 4), 0) AS report_rate
FROM campaigns
ORDER BY id;
text
   name   | clicks | orders | raw_rate | report_rate
----------+--------+--------+----------+-------------
 春季促销  |   1200 |     36 |   0.0300 |      0.0300
 新品首发  |      0 |      0 |          |      0.0000
 会员日    |    800 |     12 |   0.0150 |      0.0150
(3 rows)

步骤 5：UPSERT 增量回填（只覆盖真正带值的列）

sql
-- 只带了 email，nickname/phone 留空 → 保留旧值
INSERT INTO users (id, username, nickname, phone, email)
VALUES (1, 'zhangsan', NULL, NULL, 'zhangsan@new.com')
ON CONFLICT (id) DO UPDATE
SET
  nickname = coalesce(nullif(trim(excluded.nickname), ''), users.nickname),
  phone    = coalesce(nullif(trim(excluded.phone), ''),    users.phone),
  email    = coalesce(nullif(trim(excluded.email), ''),    users.email)
RETURNING id, username, nickname, phone, email;
text
 id | username | nickname |    phone    |       email
----+----------+----------+-------------+-------------------
  1 | zhangsan | 张三      | 13800000001 | zhangsan@new.com
(1 row)

nickname 与 phone 传的都是 NULL，经 coalesce 后保留了旧值；只有 email 被更新。

步骤 6：表达式索引验证

sql
CREATE INDEX idx_products_price_eff ON products ((coalesce(price, 0)));
ANALYZE products;

EXPLAIN (COSTS OFF)
SELECT sku FROM products WHERE coalesce(price, 0) = 0;
text
                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_products_price_eff on products
   Index Cond: (COALESCE(price, '0'::numeric) = '0'::numeric)
(2 rows)

步骤 7：清理

sql
DROP TABLE users;
DROP TABLE campaigns;
DROP TABLE products;

注意事项 ​

  • COALESCE 只判断 NULL，不判断"无效值"。空串、0、false 都会原样返回，需要先 NULLIF 归一。
sql
-- 错误预期：以为会得到 100
SELECT coalesce(0, 100);       -- 0
-- 正确：先把 0 视为无效
SELECT coalesce(nullptr_placeholder, 100);
  • 参数必须先归约到同一类型，跨类别直接报错，不要依赖隐式转换去猜：
sql
-- 错误！
SELECT coalesce(1, true);       -- ERROR: COALESCE types integer and boolean cannot be matched
-- 正确
SELECT coalesce(1::text, 'true');
  • NULLIF 只有两个参数，排除多个哨兵值只能嵌套：
sql
SELECT nullif(nullif(nullif(status, ''), 'unknown'), 'n/a') FROM t;
  • COALESCE 不能防除零，NULLIF 才能：
sql
-- 错误！
SELECT a / coalesce(b, 0) FROM t;
-- 正确
SELECT a / NULLIF(b, 0) FROM t;

  • coalesce(count(*), 0) 是无用包装，count 在空集上天然返回 0。真正需要兜底的是 sum / avg / array_agg / string_agg。

  • 短路求值只影响执行期。类型检查与语法解析仍会遍历所有参数，coalesce(1, true) 该报错还是会报错。

  • WHERE 中包裹索引列会导致普通索引失效：

sql
-- 不推荐：索引无法匹配
WHERE coalesce(price, 0) > 100
-- 推荐：拆成等价条件
WHERE price > 100 OR (price IS NULL AND 0 > 100)

  • 表达式索引要求参数不可变。coalesce(now(), t.col) 无法建索引；查询写法必须与索引表达式逐字一致。

  • DEFAULT 表达式不能引用其他列。想在默认值里做"多列兜底"是做不到的，只能用 current_setting、常量与稳定函数。

  • NULLIF 基于 = 比较，字符串比较受排序规则影响。不同 collation 下大小写与重音的相等判定可能不同，跨库迁移要留意。

最佳实践 ​

  1. 聚合结果一律用 COALESCE 兜底为业务默认值，sum / avg / array_agg 都适用，唯独 count 不必：

    sql
    SELECT coalesce(sum(amount), 0) FROM orders;
SELECT coalesce(array_agg(x ORDER BY x), '{}'::int[]) FROM t;

  2. 除法一律用 NULLIF(分母, 0) 包裹，这是消除线上 division by zero 的最低成本手段。

  3. 清洗链固定写成 coalesce(nullif(trim(x), ''), 默认值)，把空串与 NULL 一次归一并兜底，避免每个查询各写一套。

  4. UPSERT 部分更新用 coalesce(excluded.col, t.col)，语义与代码都最清晰：

    sql
    SET nickname = coalesce(nullif(trim(excluded.nickname), ''), users.nickname)

  5. 需要"兜底后的值"参与检索时建表达式索引，并保证查询写法与索引表达式一致。

  6. 不要用嵌套 CASE WHEN a IS NOT NULL THEN a ELSE b END 替代 COALESCE，多级兜底时前者冗长且更易写错：

    sql
    -- 不推荐
CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE 'x' END
-- 推荐
coalesce(a, b, 'x')

  7. 把兜底默认值抽成常量或小函数，避免在几十处查询里硬编码同一个字符串：

    sql
    CREATE OR REPLACE FUNCTION display_name(nick text, uname text)
RETURNS text LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce(nullif(trim(nick), ''), nullif(trim(uname), ''), '匿名用户');
$$;

  8. 把 0、空串这类哨兵值在设计阶段就消灭。列上加 NOT NULL DEFAULT '' 之类的约定，能减少大量运行时的 NULLIF；只有在对接外部系统时才不得不做归一。

下一节

继续学习：GREATEST / LEAST

参考链接 ​