Menu

PostgreSQL COALESCE、NULLIF 与 CASE WHEN 用法

COALESCE(a, b, ...) 返回第一个不是 NULL 的参数,NULLIF(a, b) 把一个值变成 NULL,CASE WHEN 按条件选择一个值。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

COALESCE(a, b, c) 返回参数中第一个不是 NULL 的值,所以 COALESCE(nickname, name) 在有昵称时显示昵称,否则显示名字。NULLIF(a, b) 正好相反:当 a 等于 b 时返回 NULL。CASE WHEN condition THEN value ... ELSE value END 按条件选择一个值。

Ben 和 Chen 没有昵称,所以显示的是全名。Ben 还得到了 no phone。

用默认值替换 NULL

COALESCE 就是 PostgreSQL 版的 IFNULL、ISNULL 和 NVL。因为任何和 NULL 做的运算都得到 NULL,所以要在运算前用它把 NULL 变成 0;它还能给没有任何行的聚合一个值:

不用 COALESCE 时订单 2 显示 NULL,用了之后是 80;没有任何行的求和变成了 0,而不是 NULL。

所有参数必须能归为同一种类型。对整数列写 COALESCE(quantity, 'none') 会报错“invalid input syntax for type integer”,因为 PostgreSQL 会试着把 'none' 当作整数读取。先转换列的类型:COALESCE(quantity::text, 'none')。类型转换见 CAST 页面。

用 NULLIF 把值变成 NULL

NULLIF(a, b) 在 a = b 时返回 NULL,否则返回 a。它可以防止除以零,也能把空字符串变成 NULL,让 COALESCE 来替换:

spring 的点击率是 5。summer 得到 NULL,而不是“division by zero”错误,它的空负责人变成了 unassigned。

用 CASE WHEN 选择值

这种形式按顺序检查条件,返回第一个匹配的结果。什么都没匹配上的行得到 ELSE 的值,没有 ELSE 时得到 NULL:

ORDER BY 中的 CASE 把售罄的商品排到最后,所以 mug 在最后一行。还有一种更短的形式,把一个表达式和若干值比较:CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END。它无法判断 NULL,因为 NULL = NULL 不为真。这种情况要在完整形式中用 WHEN x IS NULL。

按条件计数

在聚合函数里使用 CASE,一次扫描就能按类别计数或求和。PostgreSQL 还有 FILTER 子句,表达同样的意思更直接:

SELECT count(CASE WHEN status = 'paid' THEN 1 END) AS paid,
       count(*) FILTER (WHERE status = 'paid')      AS paid_too
FROM orders;

两者都只统计已付款的订单:count() 会跳过 CASE 对其他行返回的 NULL。

GREATEST 和 LEAST 会忽略 NULL

GREATEST(a, b, ...) 和 LEAST(a, b, ...) 返回最大和最小的参数。在 PostgreSQL 中,它们跳过 NULL 参数,只有所有参数都是 NULL 时才返回 NULL,所以 GREATEST(1, NULL, 3) 是 3。MySQL 只要有一个参数是 NULL 就返回 NULL,迁移查询时要注意这一点。完整规则见 PostgreSQL 条件表达式文档。

常见问题

PostgreSQL 中和 IFNULL、ISNULL 或 NVL 对应的是什么?

COALESCE(value, fallback)。PostgreSQL 没有 IFNULL(MySQL)、ISNULL(SQL Server)或 NVL(Oracle)函数;COALESCE 是标准 SQL,还能接收两个以上的参数。

PostgreSQL 中怎么把 NULL 替换成 0?

把列包起来:COALESCE(discount, 0)。对没有任何行的求和会返回 NULL,这时写 COALESCE(SUM(amount), 0)。

COALESCE 会计算所有参数吗?

不会。它按顺序计算参数,遇到第一个不是 NULL 的就停下,所以对这一行来说,后面的参数不会被计算。

WHERE 子句中能用 CASE 吗?

能,CASE 表达式可以出现在任何能放值的地方,包括 WHERE、ORDER BY 和聚合函数内部。不过在 WHERE 中,普通的 AND/OR 条件通常更好读。

Coddy 编程语言插图

和 Coddy 一起学编程

开始