Postgres计算与NULL的歧义

本文的非正式副标题可以是：为什么NOT NULL约束必须认真对待。避开下述复杂性的一种办法，是一开始就不存储NULL。如果某列应当始终有值，就在表结构中明确表达这一点。

先做一道小测验：下面的查询返回什么？

SELECT (NULL = NULL) = (NULL != NULL);

如果你回答NULL，答对了。NULL = NULL和NULL != NULL都未知，所以外层=比较的是未知与未知，结果仍是未知。当任一侧未知时，=、<>等比较运算符返回NULL。这就是SQL提供IS NULL而不是让你使用= NULL的原因：你无法知道两个未知值是否相等，但可以检测某个值是否未知。这里有一项历史兼容例外，文章末尾会介绍。

阅读后面的内容时，请记住：NULL不是一个值，它表示未知。

OR与AND的三值逻辑

SELECT
  NULL = NULL              AS null_eq_null,    -- NULL
  TRUE  OR  NULL           AS true_or_null,    -- t
  FALSE OR  NULL           AS false_or_null,   -- NULL
  TRUE  AND NULL           AS true_and_null,   -- NULL
  FALSE AND NULL           AS false_and_null,  -- f
  NOT NULL::boolean        AS not_null,        -- NULL
  NULL::boolean IS UNKNOWN AS is_unknown;      -- t

OR的一侧为真时，即使另一侧未知，结果仍可为真；AND的一侧为假时，即使另一侧未知，结果仍可为假。其余这些涉及NULL的组合会得到未知。

下面这个非常简单的查询，会丢掉一行你很可能希望保留的数据：

CREATE TABLE flags (id int, active boolean);
INSERT INTO flags VALUES (1, true), (2, false), (3, NULL);

-- Rows 1 and 2 only. Row 3 is unknown, so it is filtered out.
SELECT * FROM flags WHERE active OR NOT active;

在二值逻辑中，active OR NOT active是永真式，也就是对每个可能的值都为真，不可能失败。但SQL不是这样。通常的解决办法是先决定NULL应代表什么，再明确写出来：

SELECT * FROM flags WHERE COALESCE(active, false);
SELECT * FROM flags WHERE active IS NOT TRUE;   -- false and NULL
SELECT * FROM flags WHERE active IS UNKNOWN;    -- NULL only
SELECT * FROM flags WHERE active IS DISTINCT FROM true;

IS TRUE、IS NOT TRUE、IS FALSE、IS NOT FALSE和IS UNKNOWN可以让你跳出三值逻辑，它们永远不会返回NULL。对于布尔值，IS UNKNOWN与IS NULL测试相同的条件。

对应的相等性判断是IS NOT DISTINCT FROM。普通的=询问两个值是否已知相同，所以NULL = NULL得到未知。IS NOT DISTINCT FROM则把NULL当作一种值来判断两者是否相同；我喜欢把它理解为“未知状态”。两个未知状态无法区分，因此谓词为真：

SELECT
  NULL = NULL                         AS eq,            -- NULL
  NULL IS NOT DISTINCT FROM NULL      AS not_distinct,  -- t
  NULL IS DISTINCT FROM NULL          AS is_distinct,   -- f
  1 IS NOT DISTINCT FROM NULL         AS one_vs_null,   -- f
  1 IS DISTINCT FROM NULL             AS one_is_distinct; -- t

如果联接、唯一性检查或WHERE条件需要把缺失看作缺失，而不是未知，就使用它。active = NULL永远不会匹配；active IS NOT DISTINCT FROM NULL则会：

SELECT *
FROM flags
WHERE active IS NOT DISTINCT FROM NULL;  -- NULL only, same as IS NULL here

联接条件ON a.x = b.x也不会匹配两侧都为NULL的行。如果两个缺失键应算作匹配，请将该ON条件改为IS NOT DISTINCT FROM。

涉及NULL的算术运算会得到NULL

把NULL用于加法、乘法或绝对值计算，结果都是NULL。

SELECT
  1 + NULL            AS add_null,      -- NULL
  10 * NULL           AS mul_null,      -- NULL
  NULL::integer / 2   AS div_null,      -- NULL
  abs(NULL::integer)  AS abs_null,      -- NULL
  2 ^ NULL::integer   AS pow_null;      -- NULL

在报表中，这表现为空白单元格；在UPDATE中，则可能把列值清空。你很可能写过这样的语句：

UPDATE orders
SET total = quantity * unit_price;  -- total becomes NULL if either input is

在你明确知道业务规则的位置使用COALESCE。缺失数量通常可以视为零，缺失税率通常也可以视为零。但缺失单价通常不是零，应保持NULL直到有人补录。

SELECT quantity * COALESCE(unit_price, 0) AS line_total FROM orders;

有一类值得注意的例外：下文将介绍的聚合。

如果目标是从一开始就阻止NULL进入算术计算，可以对相应列施加NOT NULL约束或检查约束。

NOT IN的NULL陷阱

IN和NOT IN可以展开为一串比较。与NULL比较得到未知，而NOT IN是一连串AND：

x NOT IN (1, 2, NULL)
  ≡ x <> 1 AND x <> 2 AND x <> NULL
  ≡ TRUE-or-FALSE AND TRUE-or-FALSE AND NULL
  ≡ NULL

未知不等于真，所以相应行会消失。列表或子查询中哪怕只有一个NULL，NOT IN也不会返回任何行。

CREATE TABLE products (id int, name text);
INSERT INTO products VALUES (1, 'widget'), (2, 'gadget'), (3, 'gizmo');

CREATE TABLE discontinued (product_id int);
INSERT INTO discontinued VALUES (2), (NULL);

-- Empty. Product 1 and 3 are not discontinued, but NOT IN cannot prove it.
SELECT name
FROM products
WHERE id NOT IN (SELECT product_id FROM discontinued);

IN的影响没有那么严重，因为它是一串OR。匹配到某项时仍然为真；未匹配的值与NULL比较得到未知，因此仍可能丢行，但不会在出现一个NULL时丢掉所有行。

可靠的改写是NOT EXISTS。它在WHERE中执行相等比较，因此不会将“与NULL比较”视作匹配：

SELECT p.name
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM discontinued d
  WHERE d.product_id = p.id
);

反联接表达的是同一思路，而且通常也是你希望优化器采用的执行方式：

SELECT p.name
FROM products p
LEFT JOIN discontinued d ON d.product_id = p.id
WHERE d.product_id IS NULL;

Paul Ramsey的《反联接的兴起》讨论了这种模式的性能；若想了解规划器如何读取表，还可以阅读《如何阅读Postgres EXPLAIN：扫描类型指南》。即使还没查看执行计划，NULL的歧义也足以说明，NOT IN不适合作为默认选择。

如果必须保留NOT IN，请从子查询中排除NULL：

SELECT name
FROM products
WHERE id NOT IN (
  SELECT product_id FROM discontinued WHERE product_id IS NOT NULL
);

只有确定discontinued中的NULL应被忽略时，这样做才合适。NOT EXISTS能更清楚地表达这种含义。

聚合忽略NULL

CREATE TABLE reviews (product_id int, rating int);
INSERT INTO reviews VALUES
  (1, 5),
  (1, 1),
  (1, NULL),   -- skipped the survey
  (2, NULL),
  (2, NULL);

SELECT
  product_id,
  COUNT(*)          AS rows,
  COUNT(rating)     AS rated,
  AVG(rating)       AS avg_rating,
  SUM(rating)       AS sum_rating
FROM reviews
GROUP BY product_id
ORDER BY product_id;

 product_id | rows | rated |     avg_rating     | sum_rating
------------+------+-------+--------------------+------------
          1 |    3 |     2 | 3.0000000000000000 |          6
          2 |    2 |     0 |                    |

如果未填写的评分应计为零，就在聚合前明确处理：

SELECT product_id, AVG(COALESCE(rating, 0)) AS avg_including_blanks
FROM reviews
GROUP BY product_id;

如果它们不应计入，默认行为已经正确。错误的做法是使用SUM(x) / COUNT(*)，却期望它等于AVG(x)。还要注意类型：这里SUM(rating)和COUNT(rating)都是整数，因此除非先转换类型，否则/会截断小数。6 / 2恰好等于3；如果评分为5、1、2，整数除法结果会是2，而AVG得到2.66…。

SELECT
  AVG(rating)                            AS avg_skips_nulls,
  SUM(rating) / COUNT(*)                 AS sum_over_all_rows,
  SUM(rating)::numeric / COUNT(rating)   AS same_as_avg
FROM reviews
WHERE product_id = 1;

AVG对应SUM / COUNT(column)，而非SUM / COUNT(*)。FILTER不会改变这个结果，因为AVG(rating)本来就跳过NULL。如果希望查询明确显示这一规则，可以写FILTER (WHERE rating IS NOT NULL)。真正会改变结果的是AVG(COALESCE(rating, 0))。

SELECT
  AVG(rating) FILTER (WHERE rating IS NOT NULL) AS avg_rated,  -- same as AVG(rating)
  AVG(COALESCE(rating, 0))                      AS avg_blanks_as_zero
FROM reviews;

窗口函数与NULL

把SUM或AVG用作窗口函数时，它们仍会像用于GROUP BY时那样跳过NULL；ROW_NUMBER()仍会对该行编号。差别出现在lag、lead、first_value、last_value和nth_value：这些函数查看特定位置，如果该位置是NULL，结果就是NULL，不会寻找最近的实际值。

下面是一段丢失了一次采样的温度序列：

CREATE TABLE readings (ts int, temp numeric);
INSERT INTO readings VALUES
  (1, 20),
  (2, NULL),  -- sensor dropped a sample
  (3, 22);

SELECT
  ts,
  temp,
  lag(temp) OVER (ORDER BY ts) AS prev_temp,
  SUM(temp) OVER (ORDER BY ts) AS running_sum
FROM readings;

 ts | temp | prev_temp | running_sum
----+------+-----------+-------------
  1 |   20 |           |          20
  2 |      |        20 |          20
  3 |   22 |           |          42

过去，填补这种空缺意味着使用子查询或带过滤的DISTINCT ON。原文撰写时的计划是让Postgres 19加入SQL标准的空值处理子句：RESPECT NULLS为默认值，与原有行为相同；IGNORE NULLS则忽略空值。该子句放在函数参数与OVER之间。

SELECT
  ts,
  temp,
  lag(temp) OVER (ORDER BY ts)                 AS prev_respect,
  lag(temp) IGNORE NULLS OVER (ORDER BY ts)    AS prev_ignore
FROM readings;

 ts | temp | prev_respect | prev_ignore
----+------+--------------+-------------
  1 |   20 |              |
  2 |      |           20 |          20
  3 |   22 |              |          20

IGNORE NULLS向后搜索非空参数，对lead则向前搜索，再仅根据这些非空行应用偏移量。first_value与last_value使用同一子句。为说明它的作用，再加入传感器启动之前的时刻，使窗口帧首行为NULL：

INSERT INTO readings VALUES (0, NULL);

SELECT
  ts,
  temp,
  first_value(temp) OVER w              AS first_respect,
  first_value(temp) IGNORE NULLS OVER w AS first_ignore
FROM readings
WINDOW w AS (
  ORDER BY ts
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

 ts | temp | first_respect | first_ignore
----+------+---------------+--------------
  0 |      |               |           20
  1 |   20 |               |           20
  2 |      |               |           20
  3 |   22 |               |           20

first_respect每行都是NULL，因为窗口帧的第一行未知。first_ignore则为20，即第一个实际温度值。

这个选项只适用于lag、lead、first_value、last_value和nth_value。排名函数和窗口聚合不接受它，它们已经有各自的规则。如果希望明确写出窗口SUM或AVG排除空值的规则，仍然使用FILTER (WHERE … IS NOT NULL)。

字符串连接与NULL

SQL的||运算符相当于字符串的算术运算：输入NULL，输出就是NULL。与之相比，concat把缺失部分当作空字符串。

SELECT 'Hello, ' || NULL || '!' AS greeting;   -- NULL
SELECT NULL || 'suffix';                       -- NULL

用first_name与允许为空的middle_name构造显示名称时，中间名缺失就会使整个名称消失。

concat和concat_ws将NULL视为空字符串：

SELECT concat('Hello, ', NULL, '!');           -- Hello, !
SELECT concat_ws(' ', 'Ada', NULL, 'Lovelace'); -- Ada Lovelace

concat_ws放置分隔符时还会跳过NULL参数，因此不会产生双空格。拼接可选的地址行或姓名组成部分时，适合使用它。

如果空字符串是合适的替代值，COALESCE是另一种选择：

SELECT 'Hello, ' || COALESCE(middle_name, '') || '!';

不要未经检查就混用这两种风格。'a' || NULL得到NULL，而concat('a', NULL)得到'a'。如果应用在SQL中拼接文本，再用IS NULL表示“所有部分都缺失”，改用concat后就可能判断失误。

排序时NULL高于实际值

CREATE TABLE scores (name text, points int);
INSERT INTO scores VALUES
  ('Ada', 10),
  ('Ben', NULL),
  ('Cam', 7);

SELECT * FROM scores ORDER BY points ASC;
-- Cam   7
-- Ada  10
-- Ben   NULL

SELECT * FROM scores ORDER BY points DESC;
-- Ben   NULL
-- Ada  10
-- Cam   7

如果想要“最高分在前，缺失分数放在最后”，只写DESC会把Ben排在第一位。需要明确指定空值顺序：

SELECT * FROM scores ORDER BY points DESC NULLS LAST;
SELECT * FROM scores ORDER BY points ASC  NULLS FIRST;

NULLS FIRST和NULLS LAST也适用于索引。如果总是使用ORDER BY points DESC NULLS LAST查询，应让索引定义与之匹配。

排序时，NULL位于最大值之外，但MIN和MAX会忽略它，因此MAX(points)为10。排序与聚合采用不同的NULL规则，行为也就略有不同。

一份简短检查清单

如果计算结果看起来不对，可以检查以下情况：

WHERE丢行：谓词为未知。使用IS NULL／IS NOT NULL、IS UNKNOWN、IS NOT DISTINCT FROM、IS TRUE／IS NOT TRUE或COALESCE。
NOT IN返回空结果：列表中有NULL。改用NOT EXISTS或反联接。
合计为空：算术运算遇到了NULL。在确定业务规则的位置使用COALESCE。
平均值过高或过低：AVG跳过了NULL。比较COUNT(*)与COUNT(column)。
lag／first_value返回NULL：所查看的位置未知。对于原文介绍的Postgres 19功能，使用IGNORE NULLS。
名称或标签消失：||遇到NULL。使用concat_ws。
DESC排序时缺失值在最前：NULL按较大值处理。加上NULLS LAST。

Postgres遵循SQL标准。《9.2 比较函数与运算符》说明了比较规则，并要求那些期望expression = NULL为真的应用修改写法：

任一输入为NULL时，普通比较运算符会得到NULL（表示“未知”），而不是true或false。例如，7 = NULL得到NULL，7 <> NULL也是如此。

不要写expression = NULL，因为NULL并不“等于”NULL。NULL代表未知值，而我们不知道两个未知值是否相等。

历史兼容例外：transform_null_equals

开头的小测验返回NULL。transform_null_equals不会改变这个特定例子的结果，但会改变= NULL的行为，使它按IS NULL处理。

从6.5到7.1，Postgres曾执行这种改写，以兼容Microsoft Access生成expr = NULL的筛选表单。在pgsql-general邮件列表的《null != null ???》讨论中，Thomas Lockhart将其描述为解析器中专门帮助MSAccess用户的功能。Postgres 7.2把该设置默认改为关闭。启用时，解析器会把准确形式的expr = NULL和NULL = expr改写为expr IS NULL。

它不会改写!=、<>、IN或两个列之间的比较。

SET transform_null_equals TO on;

SELECT
  (NULL = NULL) = (NULL != NULL) AS quiz,          -- still NULL
  NULL = NULL                    AS null_eq_null, -- t
  NULL = TRUE                    AS null_eq_true, -- f
  NULL != NULL                   AS null_ne_null, -- NULL
  (NULL = NULL) = NULL          AS keyword_null; -- f

第一个内层=被改写为IS NULL，结果为真。NULL = TRUE是另一种准确形式，因此变为TRUE IS NULL，结果为假。!=保持不变，仍然未知。外层比较是true = (NULL != NULL)，右侧是表达式而非关键字NULL，所以不触发改写，小测验结果仍是未知。如果右侧直接写关键字，即(NULL = NULL) = NULL，就会变成true IS NULL，结果为假。这并没有让两个未知值相等。

你只是让Access满意，而且只有它直接写出= NULL或NULL =时才有效。保持该设置关闭，使用IS NULL。

提醒：在Postgres的众多设置中，请不要修改transform_null_equals。

既然已经理解未知值带来的歧义，就为每项计算决定未知究竟意味着什么，或者考虑它是否应该存在于表结构中。