21 NOT NULL 与 DEFAULT

使用 NOT NULL 要求必须有值，并为新行提供有用的默认值。

NOT NULL 和 DEFAULT 是最有用的两种列级规则。NOT NULL 表示该列始终有值——每次 INSERT 和 UPDATE 都必须提供一个值（或依赖默认值）。省略该列时，DEFAULT expr 会提供这个值。两者结合后，模式更容易理解：如果列设为 NOT NULL，应用程序代码就不必到处写 IF column IS NULL。

本章要点

使用 NOT NULL 强制要求有值
使用 DEFAULT expr 提供默认值
易变默认值与常量默认值的区别，以及这对大规模场景意味着什么
组合使用 NOT NULL 和 DEFAULT，确保“始终有值”
以后删除或修改默认值

示例

示例 21.1 NOT NULL 与 DEFAULT

DROP TABLE IF EXISTS tasks_example;

CREATE TABLE tasks_example (
  id          integer PRIMARY KEY,
  title       text NOT NULL,
  done        boolean NOT NULL DEFAULT false,
  priority    integer NOT NULL DEFAULT 3 CHECK (priority BETWEEN 1 AND 5),
  due_at      timestamptz,                              -- nullable
  created_at  timestamptz NOT NULL DEFAULT current_timestamp
);

-- Defaults fill in done, priority, created_at
INSERT INTO tasks_example (id, title) VALUES (1, 'Learn SQL');

-- Explicit values override the defaults
INSERT INTO tasks_example (id, title, done, priority, due_at)
VALUES (2, 'Ship lesson', true, 1, current_timestamp + interval '1 day');

SELECT * FROM tasks_example ORDER BY id;

-- Remove a default later (or change it)
ALTER TABLE tasks_example ALTER COLUMN priority SET DEFAULT 1;
ALTER TABLE tasks_example ALTER COLUMN priority DROP DEFAULT;
ALTER TABLE tasks_example ALTER COLUMN priority SET DEFAULT 3;

-- Adding NOT NULL to a column requires no NULLs to exist
-- ALTER TABLE tasks_example ALTER COLUMN due_at SET NOT NULL;  -- would fail if any due_at is NULL

NOT NULL 是代价最低、影响最大的约束之一——它消除了一整类错误（忘记检查空值、聚合结果错误、连接结果出人意料）。只要应用逻辑保证某列一定有值，就应将它标记为 NOT NULL。

DEFAULT 在插入时求值，而不是在创建表时求值。DEFAULT current_timestamp 会针对每个插入的行运行一次 current_timestamp，返回事务开始时间。DEFAULT gen_random_uuid() 会为每一行生成一个新的 UUID。

向已有表添加常量默认值（DEFAULT 0、DEFAULT 'pending'）速度很快——PostgreSQL 11+ 会把这个常量默认值存入元数据，而无需重写表。包括 STABLE 表达式 current_timestamp 在内的非易变默认值也可以使用快速路径。random() 和 clock_timestamp() 等易变默认值需要为每一行已有数据提供一个值，因此会重写表。

运行示例

运行方式：

$ psql -f source/not-null-and-default.sql postgres
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
 id |    title    | done | priority | due_at | created_at
----+-------------+------+----------+--------+-------------
  1 | Learn SQL   | f    |        3 |        | 2024-xx-xx
  2 | Ship lesson | t    |        1 | 2024-xx-xx | 2024-xx-xx
(2 rows)
 ...

常见陷阱

把 ''（空字符串）当成“缺失”——请选择一种约定，并始终保持一致。大多数团队用 NULL 表示“没有值”，把 '' 留给“有意留空”的情况。混用两者，会让查询永远处于半好半坏的状态。
默认值不会在 UPDATE 时自动重新应用。要“重置为默认值”，请使用 UPDATE t SET col = DEFAULT WHERE ...。
向已有列添加 NOT NULL 时，如果该列中可能已有空值，操作会失败。先回填该列，再执行 SET NOT NULL。
当每一列都有默认值时，INSERT INTO t DEFAULT VALUES 很有用——它让数据库填充所有值。

提示

常见模式是将 created_at timestamptz NOT NULL DEFAULT current_timestamp 与 updated_at timestamptz NOT NULL DEFAULT current_timestamp 配对使用。created_at 在 INSERT 时设置，此后不再变化；updated_at 则需要一个触发器（后文会讲）在每次 UPDATE 时将其更新。

动手练习

尝试执行 INSERT INTO tasks_example (id) VALUES (3);——title 列的 NOT NULL 应该拒绝这次插入。然后执行 INSERT INTO tasks_example (id, title, priority) VALUES (3, 'X', 9);，观察 CHECK 如何拒绝超出范围的优先级值。

21 NOT NULL 与 DEFAULT

本章要点

示例

运行示例

常见陷阱

下载