数据库基础体系 · 第 21/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

PostgreSQL 数据类型与 Schema：数组、范围、JSONB、约束和域

PostgreSQL 的 Schema 设计，不只是决定“表放在哪个命名空间”。它还包括：

• 如何选择内置数据类型；
• 如何用数组、范围和 jsonb 表达结构化值；
• 如何用约束阻止无效状态进入数据库；
• 如何用域（domain）复用标量类型及其不变量；
• 如何通过 Schema、权限和限定名控制对象解析；
• 如何理解这些机制在事务、并发、索引和故障场景下的行为。

这些能力共同决定了数据模型的边界。一个字段如果本应是“时间区间”，却被存成两个没有约束关系的时间戳；一个字段如果需要稳定的 JSON 结构，却完全依赖应用代码校验；一个可复用的格式约束被复制到几十张表中，最终都容易产生数据不一致。

本文示例以 PostgreSQL 当前稳定版本的公开语义为准，SQL 在单个 PostgreSQL 数据库中执行。除特别说明外，示例没有依赖分布式事务或特定云厂商扩展。

---

一、先区分四个层次：类型、值、约束和 Schema

1. 数据类型决定“值是什么”

类型定义了值的表示和可执行的运算。例如：

42              -- integer 或 numeric，取决于上下文
'2025-03-01'    -- 未确定上下文时通常是 unknown 字面量
ARRAY[1, 2, 3]  -- integer[]
'{"a": 1}'      -- 可能被解析为 json/jsonb，也可能只是 text

同一个字面量不一定天然具有最终类型。PostgreSQL 会结合目标列、函数参数、运算符和类型转换规则解析它。

常见类型包括：

• 标量类型：integer、numeric、text、boolean、timestamp；
• 数组类型：integer[]、text[]；
• 范围类型：int4range、daterange、tstzrange；
• 多范围类型：例如 datemultirange；
• 文档类型：json、jsonb；
• 用户定义类型：枚举、复合类型、范围类型等；
• 域：基于已有类型增加约束的类型。

2. 值和 NULL 不是一回事

NULL 表示缺失或未知，不等于：

• 空数组 {}；
• 空 JSON 对象 {}；
• JSON 的 null；
• 空范围 empty；
• 空字符串 ''。

例如：

SELECT
    NULL::text              AS sql_null,
    ''::text                AS empty_text,
    '{}'::text[]            AS empty_array,
    'null'::jsonb           AS json_null,
    '{}'::jsonb             AS empty_object,
    'empty'::int4range      AS empty_range;

这五种值的语义不同：

表达式	含义
NULL::text	SQL 值缺失或未知
''::text	已知的空字符串
'{}'::text[]	已知的空数组
'null'::jsonb	JSON 文档中的 null
'{}'::jsonb	JSON 空对象
'empty'::int4range	不包含任何元素的范围

约束、索引和查询都必须明确处理这些区别。

3. 约束决定“哪些值允许进入关系”

类型通常回答“值如何表示”，约束回答“哪些值符合业务不变量”。

例如，integer 可以表示 -1，但年龄字段可能不允许负数：

age integer CHECK (age >= 0)

约束可能作用于：

• 单个列；
• 同一行中的多个列；
• 多行之间的唯一性；
• 两张表之间的引用关系；
• 范围之间的互斥关系。

4. Schema 是对象命名空间，不是天然的租户隔离

PostgreSQL 中，Schema 是数据库内部的对象命名空间。下列表可以共存：

billing.invoice
crm.invoice

但 Schema 本身不会自动提供：

• 独立事务；
• 独立连接池；
• 独立 WAL；
• 独立备份恢复边界；
• 自动的租户级安全隔离。

如果需要安全隔离，还必须配合角色、权限、search_path 控制、行级安全策略等机制。

---

二、Schema：对象解析、权限和 search_path

1. 创建和限定 Schema

下面创建一个应用 Schema，并显式创建表：

CREATE SCHEMA IF NOT EXISTS booking;

CREATE TABLE booking.room (
    room_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_name text NOT NULL UNIQUE
);

booking.room 中：

• booking 是 Schema；
• room 是表；
• booking.room 是限定名（qualified name）。

在生产 SQL、迁移脚本和安全敏感函数中，限定名通常比依赖隐式搜索路径更可靠。

2. search_path 如何影响未限定名称

SET search_path TO booking, public;

CREATE TABLE reservation (
    reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

这里实际创建的是 booking.reservation，因为 booking 出现在搜索路径中。

查询未限定对象名时，PostgreSQL 会按 search_path 顺序查找：

SELECT * FROM reservation;

等价于尝试查找：

booking.reservation
public.reservation

如果多个 Schema 中存在同名对象，先匹配到的对象会被使用。这带来一个重要的安全边界：

SET search_path TO attacker_schema, public;
SELECT some_function();

如果 attacker_schema 中存在同名函数或对象，解析结果可能与开发者预期不同。因此：

• 安全敏感的函数应设置明确的 search_path；
• 动态 SQL 应避免拼接未校验的对象名；
• 应使用 schema.object 形式；
• 不应把 public 视为天然可信的业务命名空间。

search_path 是会话级设置，也可以通过角色或数据库默认设置影响新会话。连接池环境尤其需要注意：一个会话修改过的设置可能在后续请求中继续存在，除非连接归还时被重置或事务边界明确控制。

3. Schema 与权限

可以把业务对象放到专用 Schema，再授予应用角色必要权限：

CREATE ROLE booking_app LOGIN PASSWORD 'change-this-in-real-deployment';

GRANT USAGE ON SCHEMA booking TO booking_app;
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA booking
TO booking_app;

USAGE ON SCHEMA 只允许访问 Schema 中的对象名称，并不等于允许读写表。表权限仍需要单独授予。

Identity 列对应的序列权限在不同写法和版本环境下应实际验证；使用 GENERATED ... AS IDENTITY 可以让列定义和序列依赖关系更集中，但应用角色仍需具备插入所需的权限。

---

三、数组：一个列中的有序同类型元素

1. 数组的基本语义

PostgreSQL 数组是同一元素类型的集合，并保留元素顺序和维度信息：

SELECT
    ARRAY[10, 20, 30]::integer[] AS values,
    ARRAY['red', 'green']::text[] AS colors;

二维数组：

SELECT ARRAY[
    ARRAY[1, 2],
    ARRAY[3, 4]
]::integer[][] AS matrix;

数组的下标默认从 1 开始：

SELECT
    (ARRAY['a', 'b', 'c']::text[])[1] AS first_value,
    (ARRAY['a', 'b', 'c']::text[])[2:3] AS slice;

结果分别是：

 first_value | slice
-------------+-------
 a           | {b,c}

数组不只是“字符串列表”。它具有：

• 元素类型；
• 下标；
• 维度；
• 元素顺序；
• 可能存在的元素级 NULL。

2. 数组中的 NULL 与数组本身为 NULL

SELECT
    NULL::integer[]              AS null_array,
    '{}'::integer[]              AS empty_array,
    ARRAY[1, NULL, 3]            AS array_with_null;

三者不同：

• NULL::integer[]：整个数组缺失；
• '{}'::integer[]：数组存在，但没有元素；
• ARRAY[1, NULL, 3]：数组存在，第二个元素为 SQL NULL。

如果业务不允许元素为 NULL，必须显式约束。NOT NULL 只能阻止整个数组为 NULL，不能阻止数组内部出现 NULL。

3. 常见数组运算

SELECT
    ARRAY[1, 2, 3] @> ARRAY[2, 3] AS contains_all,
    ARRAY[1, 2, 3] && ARRAY[3, 4] AS overlaps,
    cardinality(ARRAY[1, 2, 3]) AS element_count;

预期结果：

 contains_all | overlaps | element_count
--------------+----------+---------------
 t            | t        | 3

其中：

• @> 表示左数组包含右数组中的元素；
• && 表示两个数组有共同元素；
• cardinality 返回数组所有维度的元素总数。

数组包含判断通常关心元素是否存在，不应把它误解为“右数组是左数组的连续子序列”。例如：

SELECT
    ARRAY[1, 2, 3] @> ARRAY[3, 1] AS result;

结果为 true，因为 1 和 3 都出现在左数组中，顺序不参与 @> 的集合式判断。

4. 数组适合什么，不适合什么

数组适合：

• 一行中数量有限的标签；
• 有顺序的短列表；
• 固定或相对稳定的多值属性；
• 不需要为每个元素独立建模的值。

数组不适合：

• 每个元素都需要独立主键或状态；
• 需要频繁单独更新、删除元素；
• 需要复杂的元素级外键；
• 需要高频统计每个元素的出现次数；
• 元素数量可能无限增长。

例如，订单的商品明细通常不应存为 product_ids bigint[]，因为数量、价格、折扣、税率和商品快照都属于明细行，应使用独立的 order_item 表。

---

四、范围类型：把“开始和结束”建模成一个值

1. 范围的形式化定义

一个范围可以写成：

$$R = [l, u)$$

其中：

• $l$ 是下界；
• $u$ 是上界；
• [ 或 ( 表示下界是否包含；
• ] 或 ) 表示上界是否包含。

例如：

[2025-03-01, 2025-03-03)

表示包含 3 月 1 日，但不包含 3 月 3 日。

范围的好处是“边界关系”成为同一个值的内部语义，而不是分散到两个列和若干 CHECK 中。

PostgreSQL 提供多种内置范围类型：

类型	子类型
int4range	integer
int8range	bigint
numrange	numeric
tsrange	不带时区的 timestamp
tstzrange	带时区的 timestamptz
daterange	date

2. 构造和读取范围

SELECT
    '[1, 10)'::int4range AS r,
    lower('[1, 10)'::int4range) AS lower_bound,
    upper('[1, 10)'::int4range) AS upper_bound,
    lower_inc('[1, 10)'::int4range) AS lower_inclusive,
    upper_inc('[1, 10)'::int4range) AS upper_inclusive;

预期结果：

    r    | lower_bound | upper_bound | lower_inclusive | upper_inclusive
---------+-------------+-------------+-----------------+----------------
 [1,10) | 1           | 10          | t               | f

范围运算：

SELECT
    int4range(1, 10) * int4range(5, 15) AS intersection,
    int4range(1, 10) && int4range(5, 15) AS overlaps,
    7 <@ int4range(1, 10) AS point_in_range,
    int4range(1, 10) @> 7 AS range_contains_point;

结果的核心含义是：

• 交集为 [5,10)；
• 两个范围重叠；
• 7 位于 [1,10)；
• 范围包含 7。

3. 空范围、无限边界和 NULL

SELECT
    isempty('empty'::int4range) AS is_empty,
    lower(NULL::int4range) AS null_lower,
    upper('(,10]'::int4range) AS upper_bound;

要区分：

• empty：范围存在，但没有任何元素；
• (,10]：没有下界，但有上界；
• NULL：整个范围未知或缺失。

无限边界不是普通的 NULL。例如 (,10] 表示未限制下界，而 NULL::int4range 表示范围值本身缺失。

4. 离散范围的规范化

整数、日期等离散子类型具有“相邻元素”的概念。PostgreSQL 的内置离散范围类型会使用规范化表示。例如，对日期范围：

SELECT
    '[2025-03-01,2025-03-04]'::daterange AS normalized_range,
    '[2025-03-01,2025-03-05)'::daterange AS equivalent_range;

这两个范围表示相同的日期集合：

[2025-03-01,2025-03-05)

原因是日期范围的上界可以转换为下一个日期，并采用统一的左闭右开形式。

这意味着应用不能简单依据用户输入字符串判断范围是否相等，应比较 PostgreSQL 解析后的范围值。

对于连续类型，例如 numrange 或 tstzrange，不存在同样自然的“下一个元素”，边界形式的语义更重要。

5. 时间范围中的时区问题

如果事件发生在跨时区系统中，通常应考虑：

tstzrange

而不是：

tsrange

timestamp without time zone 只表示一个没有时区信息的本地日期时间。它适合“墙上时钟时间”语义，例如每天 09:00 的营业时间；但对于全球事件、预约和截止时间，timestamptz 通常更符合实际语义。

需要注意，timestamptz 存储的是时间线上的时刻，显示时会按当前会话时区格式化。会话时区改变，显示文本可能改变，但时刻本身不一定改变。

6. 多范围类型

多范围（multirange）是多个不重叠范围的集合。例如，一个人的多个可用时间段可以表达为：

SELECT
    '{[2025-03-01,2025-03-03), [2025-03-05,2025-03-06)}'::datemultirange;

多范围适合表示：

• 多段可用时间；
• 多段已占用区间；
• 被排除的日期集合；
• 不连续的有效期。

范围和多范围都依赖底层子类型的排序与比较语义。自定义范围类型还需要为底层类型定义合适的操作支持，不能只凭字符串格式推断范围行为。

---

五、jsonb：二进制解析的 JSON 文档值

1. json 和 jsonb 的差别

PostgreSQL 同时提供 json 和 jsonb：

• json 保存输入文本的 JSON 表示，解析后可验证其语法；
• jsonb 保存分解后的二进制格式，适合查询和索引。

jsonb 不保留 JSON 文本的以下细节：

• 对象键的输入顺序；
• 空白；
• 重复键的原始形式。

重复键尤其重要：

SELECT '{"a": 1, "a": 2}'::jsonb;

结果只保留一个键，通常后出现的值生效：

{"a": 2}

因此，如果重复键本身具有业务意义，不能使用 jsonb 作为无损原文存储。

json 更接近原始 JSON 文本，但其查询和索引能力不如 jsonb 适合作为结构化查询字段。很多应用同时保存：

• 规范化列，用于稳定查询和约束；
• jsonb 扩展字段，用于变化较快的附加属性。

2. JSON 对象、数组和标量

SELECT
    '{"name":"Ada","roles":["admin","reviewer"]}'::jsonb AS document,
    jsonb_typeof('{"name":"Ada"}'::jsonb) AS object_type,
    jsonb_typeof('[1,2,3]'::jsonb) AS array_type,
    jsonb_typeof('null'::jsonb) AS json_null_type;

jsonb_typeof 返回：

object
array
null

SQL NULL 和 JSON null 仍然不同：

SELECT
    jsonb_typeof(NULL::jsonb) AS sql_null_result,
    jsonb_typeof('null'::jsonb) AS json_null_result;

前者返回 SQL NULL，后者返回字符串 null。

3. 访问 JSONB

WITH data AS (
    SELECT '{"user":{"name":"Ada","age":37},"active":true}'::jsonb AS doc
)
SELECT
    doc -> 'user' ->> 'name' AS name,
    (doc -> 'user' ->> 'age')::integer AS age,
    doc ->> 'active' AS active_text
FROM data;

运算符含义：

• -> 返回 jsonb；
• ->> 返回 text；
• #> 按路径返回 jsonb；
• #>> 按路径返回 text。

->> 返回文本后再转换为整数，可能失败：

SELECT ('{"age":"unknown"}'::jsonb ->> 'age')::integer;

这会产生输入语法错误，而不是返回 NULL。因此，对外部 JSON 进行类型转换前，应先验证结构和类型，或在应用层完成严格解析。

4. JSONB 包含运算

SELECT
    '{"a":1,"b":2}'::jsonb @> '{"a":1}'::jsonb AS object_contains,
    '{"tags":["sql","postgres"]}'::jsonb @> '{"tags":["postgres"]}'::jsonb
        AS array_contains;

@> 判断左值是否包含右值。对象包含强调键和值，数组包含具有集合式语义，不应当被理解为字符串前缀或连续子数组匹配。

字段存在可以使用：

SELECT
    '{"a":1}'::jsonb ? 'a' AS has_a,
    '{"a":1}'::jsonb ? 'b' AS has_b;

5. jsonb 不会自动提供业务 Schema

下面的列可以保存任意合法 JSON：

CREATE TABLE booking.event_log (
    event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload jsonb NOT NULL
);

NOT NULL 只保证 payload 不是 SQL NULL，它不会要求：

• 必须是对象；
• 必须存在 event_type；
• event_type 必须是字符串；
• 必须存在版本字段；
• 字段值必须满足业务范围。

可以增加结构约束：

ALTER TABLE booking.event_log
ADD CONSTRAINT event_log_payload_shape_ck
CHECK (
    jsonb_typeof(payload) = 'object'
    AND payload ? 'event_type'
    AND jsonb_typeof(payload -> 'event_type') = 'string'
);

这个约束只验证有限的结构。若要把字符串解析为日期、整数或 UUID，仍要考虑非法格式和边界值。

更稳定的字段通常应该提升为普通列：

CREATE TABLE booking.event (
    event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type text NOT NULL,
    occurred_at timestamptz NOT NULL,
    payload jsonb NOT NULL
);

此时：

• event_type 可直接建立唯一、排序或条件索引；
• occurred_at 可使用时间范围查询；
• payload 保留额外字段；
• 关键业务字段不再依赖 JSON 路径和运行时转换。

6. JSONB 索引的边界

常见索引：

CREATE INDEX event_payload_gin_idx
ON booking.event
USING gin (payload);

它适合支持一类 JSONB 结构查询，例如存在性、包含关系等。但“有 GIN 索引”不等于任意 JSON 查询都会使用该索引。

对固定路径可以使用表达式索引：

CREATE INDEX event_type_expr_idx
ON booking.event ((payload ->> 'event_type'));

查询应与表达式语义匹配：

SELECT *
FROM booking.event
WHERE payload ->> 'event_type' = 'user.created';

如果实际查询是数值比较：

WHERE (payload ->> 'attempts')::integer >= 3

则应考虑对应的表达式索引，并保证非法 JSON 值不会使查询失败。

jsonb 的 GIN 索引通常比普通 B-tree 索引占用更多空间，写入时也需要维护索引。日志型、高写入量表不应未经测量就为整个 JSON 文档建立多个宽索引。

---

六、约束：把无效状态拒绝在数据库边界之外

1. CHECK 约束的逻辑条件

对每一行，CHECK 表达式不能为 false。如果表达式为 true 或 NULL，约束通常被认为通过。

例如：

CREATE TABLE account (
    account_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    balance numeric CHECK (balance >= 0)
);

这条约束允许：

INSERT INTO account (balance) VALUES (NULL);

因为 balance >= 0 的结果是 NULL，不是 false。

如果余额不能缺失，应写成：

balance numeric NOT NULL CHECK (balance >= 0)

这说明 CHECK 和 NOT NULL 解决的是不同问题：

• NOT NULL：值不能缺失；
• CHECK：已存在的值必须满足条件。

2. NOT NULL

CREATE TABLE customer (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL
);

NOT NULL 是列级约束，不允许整个列值为 SQL NULL。对于数组、JSONB、范围等复合值，它不检查内部结构。

3. UNIQUE 和 PRIMARY KEY

CREATE TABLE app_user (
    user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username text NOT NULL UNIQUE
);

PRIMARY KEY 等价于一个或多个列的唯一性和非空要求，并为表确定逻辑身份。

UNIQUE 对 SQL NULL 的处理需要特别注意。默认情况下，多个 NULL 可以共存，因为 NULL 不等于 NULL：

CREATE TABLE example_unique (
    value text UNIQUE
);

INSERT INTO example_unique VALUES (NULL), (NULL);

这通常可以成功。

如果业务要求“包括 NULL 在内也只能有一个”，可以使用 PostgreSQL 提供的相应唯一约束选项（版本支持情况应以目标版本文档为准），或使用表达式唯一索引明确把 NULL 映射到一个哨兵值。但哨兵值必须不与合法数据冲突。

4. 外键

CREATE TABLE booking.room (
    room_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_name text NOT NULL UNIQUE
);

CREATE TABLE booking.reservation (
    reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL
        REFERENCES booking.room(room_id),
    starts_at timestamptz NOT NULL,
    ends_at timestamptz NOT NULL,
    CHECK (starts_at < ends_at)
);

外键约束要求子表中的非空引用值在父表中存在。它解决的是跨表引用完整性，而不是时间区间重叠问题。

CHECK (starts_at < ends_at) 只保证单行的两个时间点顺序正确；它不能阻止两行之间发生冲突。

5. 排他约束：阻止范围重叠

如果同一个房间不能出现重叠预约，可以把两个普通时间列改为范围列：

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking.reservation_range (
    reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL
        REFERENCES booking.room(room_id),
    booked tstzrange NOT NULL,
    CHECK (NOT isempty(booked)),
    EXCLUDE USING gist (
        room_id WITH =,
        booked WITH &&
    )
);

这条排他约束的形式是：

$$\neg \exists r_1, r_2: r_1.room\_id = r_2.room\_id \land r_1.booked \cap r_2.booked \ne \varnothing$$

也就是说，不允许存在两行，使得：

1. room_id 相等；
2. booked 范围重叠。

插入数据：

INSERT INTO booking.room (room_name)
VALUES ('A-101')
RETURNING room_id;

假设返回 1，继续执行：

INSERT INTO booking.reservation_range (room_id, booked)
VALUES (1, '[2025-03-01 10:00+00, 2025-03-01 12:00+00)');

以下插入与上一条相邻但不重叠，因此可以成功：

INSERT INTO booking.reservation_range (room_id, booked)
VALUES (1, '[2025-03-01 12:00+00, 2025-03-01 13:00+00)');

以下插入发生重叠，会被排他约束拒绝：

INSERT INTO booking.reservation_range (room_id, booked)
VALUES (1, '[2025-03-01 11:30+00, 2025-03-01 12:30+00)');

错误通常是类似：

ERROR: conflicting key value violates exclusion constraint ...

这里的 btree_gist 是扩展，用于让 room_id WITH = 这样的等值条件可以参与 GiST 排他约束。扩展安装需要数据库权限，并且是部署边界的一部分：迁移脚本、备份恢复和新环境初始化都必须确保它存在。

6. 约束检查与并发

排他约束不是应用层的“先查询再插入”。以下应用逻辑不可靠：

事务 A：查询有没有重叠预约 —— 没有
事务 B：查询有没有重叠预约 —— 没有
事务 A：插入
事务 B：插入

两个事务可能都在插入前看不到对方的未提交数据，最终形成冲突。排他约束由数据库在并发控制下处理，至少把“不允许的最终状态”变成数据库级错误，而不是依赖应用自觉。

如果一次事务内需要先暂时违反约束、提交前再恢复合法状态，可以考虑 DEFERRABLE 约束。但并非所有约束、索引或排他约束组合都适用于相同的延迟语义，必须根据具体 DDL 验证：

SET CONSTRAINTS ALL DEFERRED;

约束的默认检查时机通常是语句执行期间；可延迟约束可以推迟到事务提交时。错误因此可能从 INSERT 时移动到 COMMIT 时，调用方必须正确处理提交失败。

7. NOT VALID：先接管新数据，再验证旧数据

为大表增加约束时，可以先不扫描既有数据：

ALTER TABLE booking.reservation
ADD CONSTRAINT reservation_time_ck
CHECK (starts_at < ends_at) NOT VALID;

这会使约束对之后的新插入和更新生效，但已有违反约束的旧行仍可能存在。

清理旧数据后再执行：

ALTER TABLE booking.reservation
VALIDATE CONSTRAINT reservation_time_ck;

验证成功后，约束覆盖整个表。NOT VALID 不是“忽略约束”，而是把“新数据保护”和“历史数据清理”拆成两个阶段。

---

七、域（domain）：带可复用约束的标量类型

1. 域的定义

域是基于已有类型创建的用户定义类型，并可以附加默认值、非空要求和检查约束。

例如定义一个简单的邮箱文本域：

CREATE DOMAIN booking.email_text AS text
    NOT NULL
    CHECK (
        VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$'
    );

这里：

• booking.email_text 是域名；
• 底层类型是 text；
• VALUE 表示正在检查的域值；
• NOT NULL 禁止该域值为 SQL NULL；
• 正则表达式只是格式检查，不是完整的邮箱标准实现。

使用域：

CREATE TABLE booking.member (
    member_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email booking.email_text
);

INSERT INTO booking.member (email)
VALUES ('ada@example.com');

以下插入失败：

INSERT INTO booking.member (email)
VALUES ('not-an-email');

2. 域的检查语义

域约束适合表达“这个标量值在任何使用场景中都必须满足的条件”，例如：

• 规范化代码格式；
• 非负金额；
• 固定格式的外部标识；
• 长度和字符集要求。

但 CHECK 的三值逻辑仍然存在。对于域：

CREATE DOMAIN nonnegative_number AS numeric
    CHECK (VALUE >= 0);

如果没有写 NOT NULL，域值为 NULL 时，VALUE >= 0 的结果也是 NULL，通常可以通过。

应根据语义选择：

CREATE DOMAIN required_nonnegative_number AS numeric
    NOT NULL
    CHECK (VALUE >= 0);

3. 域与列约束的区别

域约束和表约束解决不同层次的问题。

域适合：

所有 customer_id、external_id 都必须满足同一格式

表约束适合：

开始时间必须早于结束时间

后者依赖同一行中的两个列，不能仅用一个标量域表达。

跨行和跨表条件也不应放进域。例如：

一个房间的预约不能重叠

这是表级排他约束的职责。

4. 域不是新的底层存储格式

域基于已有类型，因此通常保留底层类型的运算和转换行为，但它不是把 text 变成了一个拥有独立二进制表示的基础类型。

例如，域可以用于函数参数和表列：

CREATE FUNCTION booking.normalize_email(x booking.email_text)
RETURNS text
LANGUAGE sql
AS $$
    SELECT lower(x::text);
$$;

函数接收的是带域约束的参数。调用时传入不符合域约束的值，会在类型转换或函数调用边界产生错误。

5. 域的修改与依赖

域可能被许多列、函数和其他对象依赖。删除或修改域时，PostgreSQL 会检查依赖关系：

DROP DOMAIN booking.email_text;

如果仍有依赖，通常会失败。强制级联：

DROP DOMAIN booking.email_text CASCADE;

可能同时删除依赖对象，因此不应把 CASCADE 当作普通迁移操作。生产迁移应先查询依赖、评估影响，并在事务和备份策略内执行。

---

八、把数组、范围、JSONB、约束和域组合起来

下面定义一个更完整的预约模型：

CREATE SCHEMA IF NOT EXISTS booking;

CREATE DOMAIN booking.email_text AS text
    NOT NULL
    CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');

CREATE TABLE booking.room (
    room_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_name text NOT NULL UNIQUE
);

CREATE TABLE booking.member (
    member_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email booking.email_text UNIQUE,
    tags text[] NOT NULL DEFAULT '{}'::text[],
    profile jsonb NOT NULL DEFAULT '{}'::jsonb,

    CONSTRAINT member_tags_no_null_ck
        CHECK (array_position(tags, NULL) IS NULL),

    CONSTRAINT member_tags_size_ck
        CHECK (cardinality(tags) <= 10),

    CONSTRAINT member_profile_object_ck
        CHECK (jsonb_typeof(profile) = 'object')
);

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking.reservation (
    reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id bigint NOT NULL
        REFERENCES booking.room(room_id),
    member_id bigint NOT NULL
        REFERENCES booking.member(member_id),
    booked tstzrange NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,

    CONSTRAINT reservation_booked_nonempty_ck
        CHECK (NOT isempty(booked)),

    CONSTRAINT reservation_metadata_object_ck
        CHECK (jsonb_typeof(metadata) = 'object'),

    CONSTRAINT reservation_no_overlap_excl
        EXCLUDE USING gist (
            room_id WITH =,
            booked WITH &&
        )
);

这个模型中各机制的职责是分开的：

• email_text：复用邮箱格式约束；
• tags text[]：保存数量有限的多值标签；
• profile jsonb：保存变化较快的附加属性；
• tstzrange：保存预约时间区间；
• CHECK：防止空范围、错误 JSON 顶层类型和数组内部 NULL；
• 外键：保证房间和会员存在；
• 排他约束：保证同一房间的预约不重叠；
• Schema：组织对象、控制名称和权限边界。

插入一条有效数据：

INSERT INTO booking.room (room_name)
VALUES ('A-101')
RETURNING room_id;

假设返回 1，再插入会员：

INSERT INTO booking.member (email, tags, profile)
VALUES (
    'ada@example.com',
    ARRAY['speaker', 'vip'],
    '{"locale":"en-US","timezone":"UTC"}'::jsonb
)
RETURNING member_id;

假设返回 1，插入预约：

INSERT INTO booking.reservation
    (room_id, member_id, booked, metadata)
VALUES (
    1,
    1,
    tstzrange(
        '2025-03-01 10:00:00+00',
        '2025-03-01 12:00:00+00',
        '[)'
    ),
    '{"source":"web","confirmed":true}'::jsonb
);

以下操作分别会触发不同层面的失败：

-- 域约束失败
INSERT INTO booking.member (email)
VALUES ('invalid');

-- 数组元素为 NULL，CHECK 失败
INSERT INTO booking.member (email, tags)
VALUES ('bob@example.com', ARRAY['ok', NULL]);

-- profile 顶层不是对象，CHECK 失败
INSERT INTO booking.member (email, profile)
VALUES ('carol@example.com', '[]'::jsonb);

-- 空范围，CHECK 失败
INSERT INTO booking.reservation (room_id, member_id, booked)
VALUES (1, 1, 'empty'::tstzrange);

-- 与已有预约重叠，排他约束失败
INSERT INTO booking.reservation (room_id, member_id, booked)
VALUES (
    1,
    1,
    '[2025-03-01 11:00:00+00, 2025-03-01 13:00:00+00)'
);

这些错误不能全部由同一个异常类型表示。应用层至少应区分：

• 非法输入导致的类型或域错误；
• NOT NULL 失败；
• CHECK 失败；
• 外键失败；
• 唯一约束冲突；
• 排他约束冲突；
• 并发事务提交时发现的约束冲突。

在 PostgreSQL 客户端中，错误的 SQLSTATE、约束名称和错误位置比只匹配错误文本更适合作为程序判断依据。

---

九、索引、查询计划和数据类型选择

1. 类型选择会决定索引策略

数组、范围和 JSONB 都有专门的索引支持，但索引只对与其操作符和表达式匹配的查询有效。

数组查询：

CREATE INDEX member_tags_gin_idx
ON booking.member
USING gin (tags);

SELECT *
FROM booking.member
WHERE tags @> ARRAY['vip']::text[];

范围查询：

CREATE INDEX reservation_booked_gist_idx
ON booking.reservation
USING gist (booked);

SELECT *
FROM booking.reservation
WHERE booked && '[2025-03-01 11:00+00,2025-03-01 12:00+00)'::tstzrange;

排他约束通常已经需要维护相应索引结构，不一定需要为完全相同的访问模式再创建一个重复索引；但实际查询模式可能不同，应通过 EXPLAIN 验证。

JSONB 查询：

CREATE INDEX reservation_metadata_gin_idx
ON booking.reservation
USING gin (metadata);

SELECT *
FROM booking.reservation
WHERE metadata @> '{"source":"web"}'::jsonb;

查看实际计划：

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM booking.reservation
WHERE metadata @> '{"source":"web"}'::jsonb;

EXPLAIN ANALYZE 会实际执行查询。对 INSERT、UPDATE 或删除语句使用它时，必须注意副作用；只读查询通常更安全。

2. 不要把索引能力等同于数据模型正确性

GIN、GiST 或表达式索引只能加速满足条件的查询，不能替代：

• 非空约束；
• 类型检查；
• 外键；
• 唯一性；
• 排他性；
• 事务隔离。

例如，为 booked 建 GiST 索引并不会自动阻止两个预约重叠；阻止重叠需要排他约束。

3. 统计信息与复杂值

数组长度、JSONB 键、范围分布都会影响优化器估算。复杂字段的查询如果很关键，应：

1. 建立符合查询表达式的索引；
2. 用 EXPLAIN (ANALYZE, BUFFERS) 验证；
3. 在代表性数据量上测试；
4. 检查写入成本和索引膨胀；
5. 必要时把高频筛选字段提升为普通列。

不要仅凭“这个类型支持 GIN/GiST”推断查询一定会走索引或一定更快。

---

十、常见误解和失败表现

1. “jsonb 有类型，所以它等于业务 Schema”

错误。jsonb 保证 JSON 语法和 JSON 类型语义，但不会自动保证你的文档包含哪些字段。

CREATE TABLE t (data jsonb NOT NULL);

这只排除了 SQL NULL。data 可以是对象、数组、字符串、数字或 JSON null，除非另加约束。

2. “数组不为空，所以内部没有 NULL”

错误。以下数组整体非空，但包含一个 NULL 元素：

ARRAY[1, NULL, 3]::integer[]

NOT NULL 约束不能解决这个问题。

3. “两个时间戳加一个 CHECK 就等于时间范围”

不完全等价。下面的约束：

CHECK (starts_at < ends_at)

只解决单行边界顺序，不能解决：

同一资源的两行时间区间不能重叠

后者需要排他约束、串行化业务逻辑或其他并发控制方案。

4. “CHECK 返回 NULL 就是失败”

错误。普通 CHECK 约束主要拒绝结果为 false 的行。若业务要求值必须存在，应同时使用 NOT NULL。

5. “Schema 可以直接当租户隔离”

错误。不同 Schema 仍在同一个数据库实例和数据库内部资源边界中。它们共享数据库级事务、WAL 和许多系统资源。权限隔离必须显式配置，并验证应用连接的 search_path 与角色权限。

6. “应用先检查，再插入，就能防止冲突”

在并发下不可靠。两个事务可以同时通过相同的预检查。唯一约束、外键和排他约束应作为数据库最终一致性边界；应用预检查可以改善用户体验，但不能替代约束。

7. “把所有数据都放到 JSONB 更灵活”

灵活性会转移成本：

• 查询表达式更复杂；
• 类型转换可能在运行时失败；
• 约束难以集中；
• 字段重命名需要更新路径；
• 统计信息和索引选择更复杂；
• 关键字段不容易被外键和唯一约束保护。

如果一个 JSON 字段已经成为稳定的筛选、连接、排序或约束对象，它通常值得提升为关系列。

---

十一、迁移、事务和部署边界

Schema 对象、扩展、索引和约束都是数据库状态的一部分。迁移不应只记录“执行过某条 SQL”，还应记录：

• 目标数据库名称；
• PostgreSQL 版本；
• 所需扩展及其版本；
• 角色和权限；
• 是否在事务中执行；
• 是否允许锁表；
• 约束是否已经验证；
• 回滚或恢复路径。

例如：

BEGIN;

CREATE SCHEMA IF NOT EXISTS booking;

ALTER TABLE booking.member
ADD CONSTRAINT member_profile_object_ck
CHECK (jsonb_typeof(profile) = 'object') NOT VALID;

-- 先在应用或后续 SQL 中清理历史非法数据

ALTER TABLE booking.member
VALIDATE CONSTRAINT member_profile_object_ck;

COMMIT;

这里有几个边界：

1. DDL 在 PostgreSQL 中很多可以参与事务，但并不应假设所有外部操作也能回滚；
2. CREATE EXTENSION 是否可执行、由谁执行，取决于数据库权限和部署环境；
3. 创建索引、验证约束可能扫描大量数据并产生锁或 I/O 压力；
4. 事务提交失败时，应用必须把整个事务视为失败，而不是只重试最后一条 SQL；
5. 使用连接池时，SET search_path、时区和其他会话参数必须有明确的生命周期管理。

---

十二、如何判断某个值该用哪种结构

可以按以下语义推导，而不是从“哪个类型更灵活”开始：

情况一：值是单个标量，并且约束可复用

使用普通类型或域：

金额、代码、外部标识、格式化文本

例如非负金额可以使用带 NOT NULL 和 CHECK 的域。

情况二：值是有限数量的同类型集合

使用数组：

标签、短列表、有限的选项集合

如果元素需要独立生命周期或外键，则改用子表。

情况三：值的核心语义是连续区间

使用范围：

有效期、预约时段、数值区间

如果多个不连续区间作为一个整体出现，考虑多范围。

情况四：字段结构变化快，且不适合立刻关系化

使用 jsonb：

事件附加信息、外部系统原始属性、版本化扩展字段

但对稳定且重要的字段，使用普通列和约束表达其语义。

情况五：条件涉及多行、跨表或并发冲突

使用表级约束：

唯一性、外键、排他性

不要试图用单行 CHECK 模拟跨行并发规则。

---

PostgreSQL 的类型系统与 Schema 机制的价值，不在于提供更多字段格式，而在于把数据的真实语义放到数据库能够验证、索引和并发控制的位置上：数组表达有限的同类型集合，范围表达边界明确的区间，jsonb 表达可演化的文档，约束表达必须成立的不变量，域复用标量级规则，而 Schema 负责组织对象与控制解析和权限边界。这样设计后，应用代码不再是唯一的数据正确性防线，数据库状态也更容易被查询、诊断、迁移和恢复。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：PostgreSQL 架构全景：进程模型、共享内存、WAL 与存储布局
• 下一篇：PostgreSQL MVCC 与 Vacuum：Tuple 可见性、膨胀、冻结和诊断
• 延伸：PostgreSQL JSONB 与扩展生态：全文检索、分区、FDW 和扩展边界

官方资料

• PostgreSQL Current Documentation
• PostgreSQL Internals

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。