learnNote

主题

第 4 章 约束、视图与序列 ​

学习目标：把数据库变成「自带保安」的系统——通过约束让脏数据进不来；通过视图把复杂查询封装成「虚拟表」；通过序列得到可控的自增 ID。看完之后能回答：

  • 主键、唯一、外键、检查、排他约束 各自适合什么场景？
  • PG 独有的 EXCLUDE USING gist 怎么让会议室预订不冲突？
  • 普通视图 vs 物化视图：什么时候需要 REFRESH MATERIALIZED VIEW？
  • SERIAL vs IDENTITY：为什么 PG 10+ 推荐后者？

4.0 概览：数据质量的三道防线 ​

┌──────────────────────────────────────────────────────────────────────┐
│         数据质量金字塔（自底向上）                                    │
├──────────────────────────────────────────────────────────────────────┤
│                                                                       │
│             【应用层】 业务校验、参数检查 (易绕过)                    │
│           ──────────────────────────────────                         │
│            【约束层】 PK / FK / UNIQUE / CHECK / EXCLUDE              │
│            ↑ PG 在写入前/事务提交前自动校验                           │
│           ──────────────────────────────────                         │
│           【类型层】 列类型 / NOT NULL / 默认值                       │
│           ↑ PG 在每条 INSERT / UPDATE 时校验                          │
│                                                                       │
└──────────────────────────────────────────────────────────────────────┘

🔒 生活类比：约束就像小区的安保。

  • 类型 = 楼栋门禁卡（卡片不对刷不开）
  • PK / UNIQUE = 「同一户号一户人家」
  • FK = 「访客必须有业主签字」
  • CHECK = 「快递必须能称重」
  • EXCLUDE = 「停车位不能两台车同时停同一格」

数据进数据库前，应用层校验只能「劝阻」，约束才是「物理拦截」。约束在 DDL 上写一次，所有客户端、所有语言、所有写入路径都受保护——这是数据库自带的最高级护甲。

📌 本章约定

  • 所有示例库为 learn_pg，假设你已经 psql -h 127.0.0.1 -U postgres -d learn_pg。
  • 完整建表脚本在 04_constraint_view/init.sql，包含一个「会议室预订系统」+ 物化视图 demo。

4.1 约束系统总览 ​

PG 支持以下 6 大类约束：

┌─────────────────┬─────────────────────────────────────────────────┐
│ 约束             │ 含义                                              │
├─────────────────┼─────────────────────────────────────────────────┤
│ NOT NULL         │ 列不能为 NULL                                     │
│ PRIMARY KEY      │ 主键 = NOT NULL + UNIQUE，且每表最多一个          │
│ UNIQUE           │ 列（或多列组合）值唯一                             │
│ FOREIGN KEY      │ 引用另一张表的列，引用必须存在                     │
│ CHECK            │ 任意布尔表达式，不通过就拒绝写入                   │
│ EXCLUDE          │ PG 独有！「值之间互相排斥」（举例: 时间段不重叠）  │
└─────────────────┴─────────────────────────────────────────────────┘

约束有两种写法：列级约束（写在列定义里）和 表级约束（写在表定义末尾）。多列组合（如复合主键、跨列 CHECK）必须写表级。

sql
-- 列级写法
CREATE TABLE t1 (
    id   serial PRIMARY KEY,
    age  int    CHECK (age >= 0)
);

-- 表级写法（推荐用于复合约束 / 命名约束）
CREATE TABLE t2 (
    id    serial,
    name  text NOT NULL,
    email text,
    PRIMARY KEY (id),
    CONSTRAINT email_format CHECK (email ~ '^[^@]+@[^@]+$'),
    CONSTRAINT name_email_unique UNIQUE (name, email)
);

💡 强烈建议给约束起名字（用 CONSTRAINT 名字 ...）。约束错误信息里会带名字，排查问题快 10 倍；后续 ALTER TABLE 也好引用。

4.2 主键 PRIMARY KEY ​

4.2.1 单列主键 ​

sql
CREATE TABLE users (
    id   bigserial PRIMARY KEY,
    name text      NOT NULL
);

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (id, name) VALUES (1, 'Bob');     -- 报错：主键冲突

实操输出：

learn_pg=# INSERT INTO users (id, name) VALUES (1, 'Bob');
ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(1) already exists.

4.2.2 复合主键 ​

订单明细表用「订单号 + 行号」做主键：

sql
CREATE TABLE order_items (
    order_id bigint,
    line_no  smallint,
    sku      text NOT NULL,
    qty      int  NOT NULL,
    PRIMARY KEY (order_id, line_no)
);

📌 与 MySQL 的区别

  • MySQL InnoDB 的主键是聚簇索引 (clustered index)，行数据按主键顺序物理排列；二级索引存的是「主键值」而不是 row id。
  • PG 没有聚簇索引概念——表是堆表（heap），所有索引（包括主键索引）都存「指向 heap 行的 ctid」。可以用 CLUSTER 表名 USING 索引名 重排，但不会持续维护，新写入的行还是按时间顺序追加。

4.2.3 主键的隐式效果 ​

每个主键 PG 会自动：

  1. 创建一个 表名_pkey 的 B-Tree 唯一索引；
  2. 自动加 NOT NULL；
  3. \d 表名 时显示在最显眼的位置。
learn_pg=# \d users
                      Table "public.users"
 Column |  Type   |              Default
--------+---------+------------------------------------
 id     | bigint  | nextval('users_id_seq'::regclass)
 name   | text    |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

4.3 唯一约束 UNIQUE ​

4.3.1 基本用法 ​

sql
CREATE TABLE accounts (
    id    bigserial PRIMARY KEY,
    email text NOT NULL UNIQUE,
    phone text UNIQUE
);

INSERT INTO accounts (email, phone) VALUES ('a@x.com', '13800000001');
INSERT INTO accounts (email, phone) VALUES ('a@x.com', '13800000002');  -- 报错

4.3.2 NULL 在 UNIQUE 中的特殊行为（PG 与 MySQL 都允许多 NULL） ​

sql
INSERT INTO accounts (email, phone) VALUES ('b@x.com', NULL);
INSERT INTO accounts (email, phone) VALUES ('c@x.com', NULL);  -- ✅ 不冲突！

为什么？因为 NULL = NULL 在 SQL 中结果是 NULL（不是 true），所以「两个 NULL 不算重复」。

🆕 PG 15+ 新功能 NULLS NOT DISTINCT：把 NULL 视为相同：

sql
CREATE TABLE accounts2 (
    phone text UNIQUE NULLS NOT DISTINCT
);
INSERT INTO accounts2 VALUES (NULL);
INSERT INTO accounts2 VALUES (NULL);  -- ❌ 报错！NULL 也算重复了

4.3.3 复合唯一 ​

sql
CREATE TABLE memberships (
    user_id  bigint,
    group_id bigint,
    UNIQUE (user_id, group_id)
);

「同一个用户不能重复加入同一个组」——只看每个独立列允许重复，组合在一起才需要唯一。

4.3.4 UNIQUE vs PRIMARY KEY ​

┌──────────────┬──────────────┬──────────────┐
│              │ PRIMARY KEY  │ UNIQUE        │
├──────────────┼──────────────┼──────────────┤
│ 一张表数量   │ 0 或 1       │ 任意多个      │
│ 是否允许 NULL │ ❌ 不允许    │ ✅ 允许（多个）│
│ 是否被 FK 引用│ ✅ 默认       │ ✅ 也可以      │
│ 默认索引     │ B-Tree       │ B-Tree       │
└──────────────┴──────────────┴──────────────┘

4.4 检查约束 CHECK ​

CHECK 是「任意布尔表达式」，可以引用本行的任意列。

4.4.1 单列 CHECK ​

sql
CREATE TABLE products (
    id    bigserial PRIMARY KEY,
    name  text NOT NULL,
    price numeric(10,2) CHECK (price > 0),
    stock int CHECK (stock >= 0)
);

INSERT INTO products (name, price, stock) VALUES ('鼠标', -1, 10);
-- ERROR: new row for relation "products" violates check constraint "products_price_check"

4.4.2 跨列 CHECK（表级） ​

sql
CREATE TABLE discount_orders (
    id           bigserial PRIMARY KEY,
    list_price   numeric(10,2) NOT NULL,
    sale_price   numeric(10,2) NOT NULL,
    CONSTRAINT sale_le_list CHECK (sale_price <= list_price)
);

📌 与 MySQL 的区别：MySQL 在 8.0.16 之前 不支持 CHECK（语法允许但被静默忽略），8.0.16 起才真正生效。PG 从 1990 年代就完整支持，且支持任意复杂表达式（含调用 IMMUTABLE 函数）。

4.4.3 CHECK 的限制 ​

CHECK 表达式必须是确定性的（IMMUTABLE）：

  • 不能用 now()、random()（随机性，每次结果不同）。
  • 不能引用其他表（不能跨表 CHECK）。需要跨表逻辑时用「外键」或「触发器」。
sql
-- ❌ 错误：now() 不是 IMMUTABLE
CREATE TABLE t (created_at timestamptz CHECK (created_at <= now()));
-- ERROR: functions in check constraint expression must be marked IMMUTABLE

4.5 外键 FOREIGN KEY ​

4.5.1 基本写法 ​

sql
CREATE TABLE customers (
    id   bigserial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE orders (
    id          bigserial PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id),
    amount      numeric(10,2) NOT NULL
);

INSERT INTO customers (name) VALUES ('Alice');         -- id = 1
INSERT INTO orders (customer_id, amount) VALUES (1, 99.5);   -- ✅
INSERT INTO orders (customer_id, amount) VALUES (999, 50);   -- ❌ 引用不存在

4.5.2 ON DELETE / ON UPDATE 行为 ​

被引用方（父表）行被删/改时，引用方（子表）该怎么办？

┌─────────────────┬─────────────────────────────────────────────────┐
│ 行为             │ 含义                                              │
├─────────────────┼─────────────────────────────────────────────────┤
│ NO ACTION (默认) │ 检查时若有引用就拒绝。可在事务末尾才检查           │
│ RESTRICT         │ 立即拒绝（不可延迟）                              │
│ CASCADE          │ 父表行删 → 子表所有引用行也被删；改 → 一起改      │
│ SET NULL         │ 父表行删 → 子表 FK 列置为 NULL（要求 FK 列允许 NULL）│
│ SET DEFAULT      │ 父表行删 → 子表 FK 列置为默认值                    │
└─────────────────┴─────────────────────────────────────────────────┘

实战：

sql
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    id          bigserial PRIMARY KEY,
    customer_id bigint REFERENCES customers(id) ON DELETE CASCADE,
    amount      numeric(10,2) NOT NULL
);

「客户删除时，订单一起删」（业务上很激进，仅作示例）。

learn_pg=# DELETE FROM customers WHERE id = 1;
DELETE 1
learn_pg=# SELECT * FROM orders WHERE customer_id = 1;
 id | customer_id | amount
----+-------------+--------
(0 rows)

4.5.3 延迟约束 DEFERRABLE（PG 特色！） ​

考虑互相引用的两张表（员工有上司，上司也是员工）：

sql
CREATE TABLE employees (
    id   bigserial PRIMARY KEY,
    name text NOT NULL,
    boss_id bigint REFERENCES employees(id)
        DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
INSERT INTO employees (id, name, boss_id) VALUES (1, 'Alice', 2);   -- 当下 2 还不存在
INSERT INTO employees (id, name, boss_id) VALUES (2, 'Bob',   1);
COMMIT;     -- 在 COMMIT 时才统一检查所有外键，正常通过
关键字组合：
   DEFERRABLE INITIALLY IMMEDIATE  约束可被延迟，但默认每条 SQL 后立即检查
   DEFERRABLE INITIALLY DEFERRED   约束可被延迟，且默认事务末尾才检查
   NOT DEFERRABLE                  不可延迟（默认）

📌 MySQL InnoDB 外键 不支持延迟检查，每条 SQL 都立即校验，碰到环引用必须先关 FOREIGN_KEY_CHECKS。这在批量数据迁移、循环引用建表时非常痛苦。

4.5.4 外键索引（容易忘的性能坑！） ​

PG 不会自动给外键列建索引。父表删除/更新时会扫描子表全表，热点表必须手动加索引：

sql
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

📌 MySQL InnoDB 创建外键时会自动创建索引（其实是要求被引用列必须有索引，且 InnoDB 会在外键列创建索引）。PG 是显式控制，更灵活但容易踩坑。

4.6 排他约束 EXCLUDE（PG 独有！） ​

4.6.1 排他约束是什么？ ​

EXCLUDE 让你对**列之间的「关系」**做约束，而不仅是值相等。最经典用例：会议室预订时间段不能重叠。

传统写法：
  - INSERT 前，应用层先 SELECT 看有没有重叠 → 还没 INSERT，被并发插入怎么办？
  - 加分布式锁？复杂、性能差。
  - 加唯一约束？UNIQUE 只能判等，不能判「重叠」。

PG 的 EXCLUDE：
  - 用 GiST 索引 + 操作符 && (重叠) 来判断。
  - 数据库层物理保证「不可能存在重叠的两行」。

4.6.2 会议室预订实战 ​

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;   -- room 列 + 范围列联合 EXCLUDE 需要

CREATE TABLE bookings (
    id     bigserial PRIMARY KEY,
    room   text NOT NULL,
    period tstzrange NOT NULL,
    EXCLUDE USING gist (
        room   WITH =,        -- 同一房间
        period WITH &&        -- 时间段重叠
    )
);

-- 第一条预订正常
INSERT INTO bookings (room, period) VALUES
    ('A101', tstzrange('2026-04-17 09:00+08', '2026-04-17 11:00+08'));

-- 同房间、同时间段 → 直接报错
INSERT INTO bookings (room, period) VALUES
    ('A101', tstzrange('2026-04-17 10:00+08', '2026-04-17 12:00+08'));

实操输出：

learn_pg=# INSERT INTO bookings (room, period) VALUES
learn_pg-#     ('A101', tstzrange('2026-04-17 10:00+08', '2026-04-17 12:00+08'));
ERROR:  conflicting key value violates exclusion constraint "bookings_room_period_excl"
DETAIL:  Key (room, period)=(A101, ["2026-04-17 10:00:00+08","2026-04-17 12:00:00+08"))
   conflicts with existing key (room, period)=(A101, ["2026-04-17 09:00:00+08","2026-04-17 11:00:00+08")).

不同房间或不重叠的时间正常通过：

sql
INSERT INTO bookings (room, period) VALUES
    ('A101', tstzrange('2026-04-17 11:00+08', '2026-04-17 13:00+08')),  -- ✅ 不重叠
    ('B202', tstzrange('2026-04-17 10:00+08', '2026-04-17 12:00+08'));  -- ✅ 不同房间

4.6.3 EXCLUDE 通用语法 ​

EXCLUDE USING <索引方法> (
    列 WITH 操作符,
    列 WITH 操作符,
    ...
) WHERE (可选过滤条件)

含义：对任意两行，若每个 (列, 操作符) 都同时满足，则发生「冲突」，约束拒绝。

典型用法：

场景操作符索引
时间段不重叠&&gist
圆形地理区域不重叠&&gist (geometry)
整数区间不相邻不重叠`--或&&`
同 group 内某列唯一=btree (相当于复合 UNIQUE，不常用)

4.6.4 EXCLUDE 也可以延迟 ​

sql
ALTER TABLE bookings ADD CONSTRAINT b_excl
    EXCLUDE USING gist (room WITH =, period WITH &&)
    DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
SET CONSTRAINTS b_excl DEFERRED;
-- 这里可以临时插入「会冲突」的中间状态行
COMMIT;     -- 必须在 COMMIT 前修正一致，否则报错

📌 MySQL 没有 EXCLUDE 约束。要做「时间段不重叠」只能：① 应用层加锁；② 业务表引入冗余字段 + 复合 UNIQUE（不灵活）；③ 用触发器 + 自定义检查（性能差）。这是 PG 在「业务约束直接落到数据库」上的杀手级特性。

4.7 修改约束：ALTER TABLE 全家桶 ​

sql
-- 加约束
ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0);
ALTER TABLE orders   ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

-- 删约束
ALTER TABLE products DROP CONSTRAINT price_positive;

-- 添加 NOT NULL
ALTER TABLE products ALTER COLUMN name SET NOT NULL;
-- 移除 NOT NULL
ALTER TABLE products ALTER COLUMN name DROP NOT NULL;

-- 加 NOT VALID（仅校验后续写入，不校验已有数据 - 适合上线大表）
ALTER TABLE huge_table ADD CONSTRAINT old_check CHECK (col >= 0) NOT VALID;
ALTER TABLE huge_table VALIDATE CONSTRAINT old_check;       -- 后续异步校验旧数据

NOT VALID 是 PG 让大表上线约束的常见技巧，避免一次锁全表扫描。

4.8 视图 VIEW ​

视图 = 保存在数据库里的 SELECT 语句，用起来像表，每次查询时实时执行底层 SQL。

📺 生活类比：视图 = 监控摄像头的实时画面（拍到啥就播啥）；物化视图 = 摄像头每隔一段时间「截一张图」存下来，看的是截图（快但有延迟）。

4.8.1 普通视图 ​

sql
CREATE TABLE sales (
    id       bigserial PRIMARY KEY,
    region   text,
    amount   numeric(10,2),
    sold_at  timestamptz NOT NULL DEFAULT now()
);

CREATE VIEW ch4_v_sales_by_region AS
SELECT region, SUM(amount) AS total, COUNT(*) AS cnt
FROM sales
GROUP BY region;

-- 用起来跟表一样
SELECT * FROM ch4_v_sales_by_region ORDER BY total DESC;

每次 SELECT * FROM ch4_v_sales_by_region 都会重新执行底层的 GROUP BY，慢查询照样慢。

4.8.2 可更新视图（PG 自动判定） ​

如果视图满足以下条件，PG 允许直接 INSERT/UPDATE/DELETE 该视图，并自动转发到底层表：

  • 只引用一张表
  • 没有 GROUP BY、HAVING、DISTINCT、UNION、窗口函数
  • SELECT 列表里都是普通列（不是表达式）
sql
CREATE VIEW v_active_users AS
SELECT id, name, email FROM users WHERE active = true;

UPDATE v_active_users SET email = 'new@x.com' WHERE id = 1;   -- ✅ 转发到底层 users 表
INSERT INTO v_active_users (name, email) VALUES ('Eve', 'e@x.com');
-- 注意：active 列没设置，会用底层表的默认值

4.8.3 WITH CHECK OPTION ​

防止「INSERT/UPDATE 后变得视图本身查不到」的怪事：

sql
CREATE VIEW v_active_users2 AS
SELECT id, name, email, active FROM users WHERE active = true
WITH CHECK OPTION;

UPDATE v_active_users2 SET active = false WHERE id = 1;
-- ❌ ERROR: new row violates check option for view "v_active_users2"

4.8.4 物化视图 MATERIALIZED VIEW（PG 特色） ​

物化视图把 SELECT 结果真实存进磁盘，查询时直接读这份「快照」，不用重新算。代价是数据会过期，需要手动 REFRESH。

sql
CREATE MATERIALIZED VIEW ch4_mv_daily_sales AS
SELECT
    date_trunc('day', sold_at)::date AS day,
    region,
    SUM(amount) AS total,
    COUNT(*)    AS cnt
FROM sales
GROUP BY 1, 2
WITH DATA;          -- 立即填充数据；WITHOUT DATA 只建结构

-- 给物化视图加索引（关键！）
CREATE UNIQUE INDEX idx_ch4_mv_daily_sales ON ch4_mv_daily_sales (day, region);

-- 查询非常快
SELECT * FROM ch4_mv_daily_sales WHERE day = '2026-04-17';

-- 刷新（默认会锁表，期间 SELECT 阻塞）
REFRESH MATERIALIZED VIEW ch4_mv_daily_sales;

-- 并发刷新（不锁读，但要求物化视图有 UNIQUE 索引！）
REFRESH MATERIALIZED VIEW CONCURRENTLY ch4_mv_daily_sales;
┌─────────────────────────────────────────────────────────────────┐
│                  普通视图 vs 物化视图                              │
├─────────────────────┬──────────────────┬──────────────────────┤
│                     │   VIEW            │   MATERIALIZED VIEW   │
├─────────────────────┼──────────────────┼──────────────────────┤
│ 是否存数据           │   ❌ 不存          │   ✅ 真实存磁盘        │
│ 查询速度             │   等同于跑底层 SQL │   等同于查表 (快)      │
│ 数据新鲜度           │   实时             │   依赖最近一次 REFRESH │
│ 占用空间             │   几乎为 0         │   等同于结果集大小      │
│ 是否可加索引          │   ❌（视图本身）   │   ✅ 任意索引          │
│ 写入                │   可更新视图能写    │   ❌ 不能直接写         │
│ 适用场景             │   实时但不复杂的封装│   重计算 + 容许延迟    │
└─────────────────────┴──────────────────┴──────────────────────┘

📌 与 MySQL 的区别：MySQL 没有原生物化视图！要实现类似效果只能：① 用普通表 + 定时 INSERT INTO ... SELECT；② 用 SUMMARY TABLE 第三方工具；③ 用 InnoDB 的内存表手撸缓存。PG 的物化视图 + REFRESH CONCURRENTLY 是开箱即用的「报表加速器」。

4.8.5 物化视图刷新策略 ​

┌─────────────────────────────────────────────────────────────┐
│                     刷新策略选择                               │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  REFRESH MATERIALIZED VIEW                                   │
│    - 锁 ACCESS EXCLUSIVE，刷新期间所有 SELECT 阻塞              │
│    - 重新执行底层 SQL，全量重写                                │
│    - 不要求 UNIQUE 索引                                       │
│                                                              │
│  REFRESH MATERIALIZED VIEW CONCURRENTLY                      │
│    - 锁 EXCLUSIVE 但不锁 SELECT，期间可读                      │
│    - 通过比对新旧结果，DELETE/INSERT 差异行                    │
│    - **必须** 有至少一个 UNIQUE 索引                          │
│    - 速度比 FULL 慢，因为要做差异计算                         │
│                                                              │
└─────────────────────────────────────────────────────────────┘

真实场景：报表表（销售日总额、用户行为聚合），每 5 分钟用 pg_cron 触发 REFRESH MATERIALIZED VIEW CONCURRENTLY，业务查询无感。

4.9 序列 SEQUENCE ​

序列是 PG 中独立于表的「自增计数器对象」。bigserial 列其实就是绑了一个序列。

4.9.1 显式创建与使用 ​

sql
CREATE SEQUENCE order_no_seq
    START 10000
    INCREMENT 1
    MINVALUE 10000
    MAXVALUE 99999999
    CACHE 50
    CYCLE;            -- 到 MAX 后回 MIN（一般不要用 CYCLE）

SELECT nextval('order_no_seq');     -- → 10000
SELECT nextval('order_no_seq');     -- → 10001
SELECT currval('order_no_seq');     -- → 10001  (当前会话的最近一次)
SELECT lastval();                   -- → 10001  (当前会话最近一次任意 nextval)
SELECT setval('order_no_seq', 50000);   -- 把序列重置到 50000

⚠️ nextval 不受事务保护：BEGIN; SELECT nextval(...); ROLLBACK; 之后序列不会回退！这是为了避免「事务回滚导致后续插入冲突」。结果就是：序列号会跳号。

4.9.2 序列与表的绑定 OWNED BY ​

sql
ALTER SEQUENCE order_no_seq OWNED BY orders.no;
-- 含义：order_no_seq 是 orders.no 的「附属物」，DROP TABLE orders 会一起删掉序列

ALTER SEQUENCE order_no_seq OWNED BY NONE;
-- 解绑：序列变成「独立对象」，DROP TABLE 时不会被删

4.9.3 序列的 cache 与多会话跳号 ​

sql
CREATE SEQUENCE my_seq CACHE 100;

PG 会一次性给当前后端「预留」100 个值。如果后端崩溃，这 100 个就白白丢掉了。所以：

  • OLTP 业务：CACHE 1 或不设，避免大量跳号；
  • 批量插入：可以 CACHE 1000+ 提速。

4.10 SERIAL vs IDENTITY：终极对比 ​

4.10.1 SERIAL（PG 9.x 时代主流） ​

sql
CREATE TABLE t1 (
    id   serial PRIMARY KEY,
    name text
);
-- 等价于：
CREATE SEQUENCE t1_id_seq;
CREATE TABLE t1 (
    id   integer NOT NULL DEFAULT nextval('t1_id_seq'),
    name text,
    PRIMARY KEY (id)
);
ALTER SEQUENCE t1_id_seq OWNED BY t1.id;
  • serial 是伪类型，写完会展开成上面这一坨。
  • 列还是 integer / bigint，没有特殊的「这是自增列」标记。
  • 显式指定 id 总是允许：INSERT INTO t1 (id, name) VALUES (1, 'X') 不会触发序列，导致后续序列与现实脱节，引发主键冲突。

4.10.2 IDENTITY（PG 10+ 推荐） ​

sql
CREATE TABLE t2 (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text
);

-- 或宽松版：
CREATE TABLE t3 (
    id   bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text
);
┌──────────────────────┬──────────────────────────────────────────┐
│  GENERATED ALWAYS     │  完全由系统生成；INSERT 时手工指定 → 报错  │
│  GENERATED BY DEFAULT │  系统默认生成；INSERT 时手工指定 → 接受    │
└──────────────────────┴──────────────────────────────────────────┘

ALWAYS 拒绝手工指定，最安全；BY DEFAULT 行为接近 serial，但仍是规范的 IDENTITY。

sql
-- ALWAYS 列强制覆盖（一般用于数据导入）
INSERT INTO t2 (id, name) OVERRIDING SYSTEM VALUE VALUES (100, 'imported');

4.10.3 关键区别速查 ​

维度SERIALIDENTITY
标准合规PG 自创SQL:2003 标准
数据字典展示看到的是「DEFAULT nextval(...)」看到的是「GENERATED ... AS IDENTITY」
手工指定保护❌ 总能塞✅ ALWAYS 模式拒绝
序列权限单独管自动跟随列权限
ALTER 类型复杂（要先删默认值再改类型）ALTER COLUMN id SET GENERATED ... 干净
CREATE TABLE LIKE序列不会复制自动复制
跨数据库迁移别的数据库不认通用语法
推荐度⭐⭐ (兼容老代码)⭐⭐⭐⭐⭐ (新表用这个)

4.10.4 与 MySQL AUTO_INCREMENT 的对比 ​

┌──────────────────────┬──────────────────┬─────────────────────────┐
│                       │  MySQL            │  PG IDENTITY             │
├──────────────────────┼──────────────────┼─────────────────────────┤
│ 计数器存储位置         │  表的元数据        │  独立 SEQUENCE 对象       │
│ 重启后行为             │  可能跳号(取决版本)│  不会跳号(但回滚会跳)     │
│ 一张表能有几个         │  最多 1 个         │  多个列都能用            │
│ 显式指定               │  允许总是接受       │  ALWAYS 模式拒绝         │
│ 重置                   │  ALTER ... AUTO_  │  ALTER SEQUENCE ... RES  │
│                       │  INCREMENT = X    │  TART WITH X             │
│ 多表共享同一计数器      │  ❌ 不行           │  ✅ 可以（手工建 SEQ + DEFAULT）│
└──────────────────────┴──────────────────┴─────────────────────────┘

4.11 综合实战：会议室预订系统 ​

完整代码在 04_constraint_view/init.sql。这里讲设计思路。

约束亮点：

sql
CREATE TABLE rooms (
    id        bigserial PRIMARY KEY,
    name      text NOT NULL UNIQUE,
    capacity  smallint NOT NULL CHECK (capacity BETWEEN 1 AND 500),
    has_screen boolean NOT NULL DEFAULT false
);

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
    id      bigserial PRIMARY KEY,
    room_id bigint NOT NULL
            REFERENCES rooms(id) ON DELETE CASCADE,
    booker  text NOT NULL CHECK (length(booker) BETWEEN 1 AND 64),
    period  tstzrange NOT NULL,
    CONSTRAINT period_in_business_hours
        CHECK (
            EXTRACT(hour FROM lower(period)) >= 8 AND
            EXTRACT(hour FROM upper(period)) <= 22
        ),
    CONSTRAINT no_overlap_in_room
        EXCLUDE USING gist (
            room_id WITH =,
            period  WITH &&
        )
);

每一条都是数据库自动校验，应用层一行 try/except 就够了，永远不会出现「同一个会议室同一时段两个人都看到自己抢到了」的情况。

4.12 本章小结 ​

┌────────────────────────────────────────────────────────────────┐
│                        本章核心要点                              │
├────────────────────────────────────────────────────────────────┤
│                                                                 │
│  ① 6 大约束：NOT NULL / PK / UNIQUE / FK / CHECK / EXCLUDE      │
│     约束在数据库层执行，永远比应用层校验更可靠                    │
│                                                                 │
│  ② UNIQUE 中 NULL 默认互不冲突，PG 15+ 用 NULLS NOT DISTINCT 改 │
│                                                                 │
│  ③ FK 不会自动建索引，热点表手动加 idx                           │
│  ④ FK 可 DEFERRABLE，PG 独家便利环引用建表                       │
│                                                                 │
│  ⑤ EXCLUDE USING gist —— PG 杀手级约束                          │
│     一条 DDL 解决「会议室不冲突 / 房间不重叠 / 价格区间不交叉」    │
│                                                                 │
│  ⑥ 视图 = 实时 SQL；物化视图 = 落地快照 + REFRESH                │
│     CONCURRENTLY 刷新需要 UNIQUE 索引但不锁读                    │
│                                                                 │
│  ⑦ PG 10+ 一律用 IDENTITY 替代 SERIAL                            │
│     - 标准 SQL                                                  │
│     - ALWAYS 拒绝手工塞值，避免序列脱节                           │
│     - 权限自动跟随                                              │
│                                                                 │
└────────────────────────────────────────────────────────────────┘

4.13 实操：跑一遍配套代码 ​

实战代码见 04_constraint_view/code/：

  • 01_constraints.py —— 演示 6 大约束被触发时的报错
  • 02_materialized_view.py —— 普通视图 vs 物化视图查询性能对比 + REFRESH CONCURRENTLY
  • 03_identity_serial.py —— SERIAL vs IDENTITY 行为差异，含 OVERRIDING SYSTEM VALUE

启动前先：

bash
psql -h 127.0.0.1 -U postgres -d learn_pg -f 04_constraint_view/init.sql

浏览器演示见 04_constraint_view/demo.html：

  • ① 会议室预订冲突检测（可拖动时段，模拟 EXCLUDE 拒绝）
  • ② 视图 vs 物化视图刷新过程动画

🎮 配套演示 ​

用浏览器打开 ./04_constraint_view/demo.html，跟着可视化动画再走一遍本章核心概念。

配套代码在 ./04_constraint_view/code/，每个脚本都可以独立 python xxx.py 运行，先跑 init.sql 准备数据。

4.14 面试高频题 ​

Q1：UNIQUE 约束的列里允许多个 NULL 吗？为什么？PG 15 有什么变化？ ​

考察点：对 SQL NULL 三值逻辑与 PG 新特性的掌握。

标准答案：

默认行为：允许多个 NULL。

原因要回到 SQL 标准对 NULL 的定义：NULL = NULL 的结果不是 true，而是 NULL（unknown）。UNIQUE 约束的判定逻辑是「两行的相应列是否相等」，相等比较结果不是 true 就不算重复。所以两行都为 NULL 的列，比较结果是 NULL，不算重复，可以同时存在多个 NULL 行。

sql
CREATE TABLE t (phone text UNIQUE);
INSERT INTO t VALUES (NULL), (NULL), (NULL);  -- 全部成功

这与「主键 PRIMARY KEY」不同：主键 = UNIQUE + NOT NULL，因此主键列根本不允许 NULL。

PG 15 新特性 NULLS NOT DISTINCT：

sql
CREATE TABLE t2 (phone text UNIQUE NULLS NOT DISTINCT);
INSERT INTO t2 VALUES (NULL);
INSERT INTO t2 VALUES (NULL);
-- ❌ ERROR: duplicate key value violates unique constraint

NULLS NOT DISTINCT 让 NULL 视为「彼此相同」，符合很多业务的真实期望（比如「每个人只能绑一个手机号，没绑过的也只能算一行 NULL」）。

与 MySQL 对比：MySQL InnoDB 的 UNIQUE 也允许多 NULL，行为一致。但 MySQL 没有 NULLS NOT DISTINCT 选项，要实现「NULL 也算重复」只能用「业务层占位符 + 触发器」绕。

易错点：业务上常错以为 UNIQUE 就「天然能保证唯一」，结果上线后发现脏数据全是 NULL。要么列加 NOT NULL，要么 PG 15+ 上 NULLS NOT DISTINCT，要么应用层把空值统一映射成占位符。

Q2：什么是 EXCLUDE 排他约束？写一个「会议室不能重复预订」的完整 SQL。 ​

考察点：PG 独有特性的掌握，以及应用约束设计能力。

标准答案：

EXCLUDE 是 PG 的「关系性排他约束」：对任意两行，如果指定的多组 (列, 操作符) 都同时成立，就视为冲突，约束阻止写入。

普通 UNIQUE 只能判「列值相等」，无法表达「时间段重叠」「区间交叉」「半径重合」这类关系。EXCLUDE 通过 GiST/SP-GiST 索引 + 自定义操作符（&& 重叠、-|- 相邻、几何 &&）补齐这一能力。

完整建表：

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;   -- 让 = 操作符也能进 GiST 索引

CREATE TABLE bookings (
    id     bigserial PRIMARY KEY,
    room   text NOT NULL,
    period tstzrange NOT NULL,
    EXCLUDE USING gist (
        room   WITH =,        -- 同一房间
        period WITH &&        -- 时间段重叠
    )
);

INSERT 冲突时数据库直接报错：

ERROR:  conflicting key value violates exclusion constraint "bookings_room_period_excl"
DETAIL:  Key (room, period)=(A101, [...10:00, 12:00))
        conflicts with existing key (room, period)=(A101, [...09:00, 11:00))

底层原理：PG 在 INSERT/UPDATE 时，根据 EXCLUDE 索引去查「是否存在另一行使所有 (列, 操作符) 都成立」。GiST 索引天然支持范围/几何这类「重叠查询」，O(log N) 即可完成。

对比 MySQL 实现等价功能：

  1. 应用层加分布式锁（Redis / ZooKeeper）→ 复杂、性能差。
  2. 业务表 + 复合 UNIQUE 维护「冗余的离散时段」（比如把每小时拆一行）→ 不灵活、表膨胀。
  3. 触发器 + BEFORE INSERT 自定义查重 → 性能差，难以延迟、并发竞争依然存在。

加分项：能讲清楚「DEFERRABLE 让 EXCLUDE 也能在事务末尾才检查」「EXCLUDE 也支持 WHERE 子句过滤（部分排他）」「EXCLUDE 与 INSERT ... ON CONFLICT 不兼容，因为 ON CONFLICT 只支持 unique 索引」。

Q3：物化视图和普通视图有什么区别？什么场景用哪个？REFRESH CONCURRENTLY 的前提是什么？ ​

考察点：视图设计选型与运维。

标准答案：

维度VIEWMATERIALIZED VIEW
是否真实存数据否，只存 SELECT 定义是，结果落到磁盘
查询性能等同跑底层 SQL等同查表，可加索引
数据新鲜度实时取决于上次 REFRESH 时间
写入满足条件可更新视图直接写不能直接写
占用空间几乎为 0等于结果集大小

适用场景：

  • VIEW：业务层 SQL 的封装、权限隔离、抽象底层表结构。例：把多张表 JOIN 成「业务实体视图」给前端用，每次都要最新数据。
  • MATERIALIZED VIEW：聚合代价高 + 容许分钟级延迟的报表。例：日销售汇总、用户行为聚合、TOP100 排行。

REFRESH 两种模式：

sql
REFRESH MATERIALIZED VIEW mv;                 -- 锁全表（ACCESS EXCLUSIVE）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv;    -- 不锁读

CONCURRENTLY 的前提：物化视图必须有至少一个 UNIQUE 索引。原理是 PG 用这个 UNIQUE 索引做新旧结果的「行比对」，只 DELETE/INSERT 差异行，不全表覆盖。

代价：CONCURRENTLY 比 FULL 刷新慢，因为多了「比对」开销；但生产几乎都用它，因为期间业务读不阻塞。

与 MySQL 对比：MySQL 没有原生物化视图。要实现等价功能，常见方案：

  1. 手撸普通表 + 定时 INSERT INTO summary SELECT ... FROM ... → 自己处理增量、并发问题。
  2. 用 INFORMATION_SCHEMA.SUMMARY 类企业版扩展（PerconaServer 提供）。
  3. 引入 ClickHouse / Doris 这类 OLAP 系统旁路加速。

PG 一行 DDL + pg_cron 定时任务就解决了。

Q4：SERIAL 和 IDENTITY 有什么区别？为什么 PG 10+ 推荐 IDENTITY？ ​

考察点：PG 现代化用法 + SQL 标准合规。

标准答案：

SERIAL 是 PG 的伪类型，本质是「整型 + 序列 + DEFAULT 表达式 + OWNED BY」的语法糖：

sql
CREATE TABLE t (id serial PRIMARY KEY);
-- 实际执行：
CREATE SEQUENCE t_id_seq;
CREATE TABLE t (
    id integer NOT NULL DEFAULT nextval('t_id_seq'),
    PRIMARY KEY (id)
);
ALTER SEQUENCE t_id_seq OWNED BY t.id;

IDENTITY 是 SQL:2003 标准语法，PG 10 起支持：

sql
CREATE TABLE t (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY     -- 强烈推荐
);

CREATE TABLE t2 (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- 接近 SERIAL 的宽松版
);

关键差异：

  1. 手工塞值的保护

    • SERIAL：INSERT INTO t (id, ...) VALUES (1, ...) 总是允许，会跳过序列，导致后续 nextval 撞 PK。
    • IDENTITY ALWAYS：INSERT INTO t (id, ...) VALUES (1, ...) 直接报错，必须显式 OVERRIDING SYSTEM VALUE 才能写。
    • 这避免了运维事故（比如导入旧数据时不小心覆盖了序列）。

  2. 权限管理

    • SERIAL：序列是独立对象，权限要单独 GRANT USAGE ON SEQUENCE。常见坑：给了表的 INSERT 但没给序列 USAGE，写入失败。
    • IDENTITY：序列与列绑定，权限自动跟随列权限。

  3. 类型修改

    • SERIAL：要把 serial 改成 bigserial，要么删默认值再改类型再加默认值，繁琐。
    • IDENTITY：ALTER TABLE t ALTER COLUMN id SET GENERATED ALWAYS 一句话。

  4. 跨数据库迁移

    • IDENTITY 是标准 SQL，Oracle、DB2、SQL Server 都认。
    • SERIAL 只 PG 认。

  5. CREATE TABLE LIKE

    • SERIAL 列复制时序列不复制，新表的 id 会沿用旧序列（可能撞）。
    • IDENTITY 行为更干净（复制还是新建可控）。

实战建议：

  • 新项目 / 新表：一律 IDENTITY。
  • 老代码改造：可保留 SERIAL，但避免再写新 SERIAL 列。
  • 数据导入场景：用 BY DEFAULT AS IDENTITY 或 OVERRIDING SYSTEM VALUE。

与 MySQL 对比：MySQL 的 AUTO_INCREMENT 是表的元数据，不是独立对象，最像 SERIAL 但更轻量。MySQL 没有「拒绝手工指定」的 IDENTITY ALWAYS 机制。

Q5：PG 的外键和 MySQL 比有什么不同？容易踩什么坑？ ​

考察点：FK 实现细节与跨库经验。

标准答案：

相同点：

  • 都校验「引用值必须存在于父表」。
  • 都支持 ON DELETE CASCADE / RESTRICT / SET NULL / SET DEFAULT / NO ACTION。

关键不同：

  1. FK 列是否自动建索引

    • MySQL InnoDB：自动为外键列创建索引（如果还没有）。
    • PG：不会自动建索引！父表删/改时会全表扫子表，热点表必须手动 CREATE INDEX。

  2. 延迟检查 DEFERRABLE

    • MySQL：不支持，每条 SQL 立即检查。环引用建表必须先 SET FOREIGN_KEY_CHECKS = 0。
    • PG：原生支持 DEFERRABLE INITIALLY DEFERRED，环引用、批量数据迁移很方便。

  3. NOT VALID 上线

    • PG：可 ALTER TABLE ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID，先只对新数据生效，再异步 VALIDATE CONSTRAINT 校验旧数据。生产大表加 FK 不停机。
    • MySQL 没有等价开关，要么短暂 FOREIGN_KEY_CHECKS = 0 要么停机加。

  4. 跨数据库（schema）

    • PG：FK 可以跨 schema（同库），不可跨数据库。
    • MySQL：FK 只在同一库内。

  5. 触发开销 / 锁

    • PG 的 FK 检查通过隐藏触发器实现（pg_trigger 里看得到）。父表上有 ROW SHARE 锁。
    • MySQL InnoDB 的 FK 检查在引擎层完成，行为更紧凑。

典型坑：

  • 忘记给 FK 列加索引：父表的 DELETE/UPDATE 性能巨差，几秒一条。生产日志会看到大量子表全表扫。
  • CASCADE 误用：业务上「客户删除 → 订单自动消失」往往是错的。建议用 RESTRICT 或 SET NULL，强制前端先做关联清理。
  • FK 列与父表 PK 类型不一致：导致每次比较都隐式转型，索引失效。例：父 bigint 子写成 int。
  • 大事务 + 大量 FK：PG 在 COMMIT 时一次性检查所有延迟约束，可能瞬间 OOM。

Q6：CHECK 约束的限制是什么？「订单总价 = 商品行总价之和」这种跨表约束怎么实现？ ​

考察点：CHECK 的边界，跨行/跨表约束的解决方案。

标准答案：

CHECK 的限制：

  1. 表达式必须 IMMUTABLE：不能用 now()、random()、不能调用 STABLE/VOLATILE 函数。
  2. 不能引用 其他行（行级约束只看自己这一行）。
  3. 不能引用 其他表。

也就是说，CHECK 适合「单行内字段间关系」，例如：

sql
CHECK (price > 0)
CHECK (sale_price <= list_price)
CHECK (start_date < end_date)
CHECK (length(name) BETWEEN 1 AND 64)

跨表 / 跨行约束的实现方案：

  1. 触发器（最通用）

    sql
    CREATE OR REPLACE FUNCTION check_order_total()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    expected numeric;
BEGIN
    SELECT COALESCE(SUM(price * qty), 0)
      INTO expected FROM order_items WHERE order_id = NEW.id;
    IF NEW.total <> expected THEN
        RAISE EXCEPTION 'order.total (%) != sum(items) (%)', NEW.total, expected;
    END IF;
    RETURN NEW;
END $$;
CREATE CONSTRAINT TRIGGER trg_check_total
    AFTER INSERT OR UPDATE ON orders
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW EXECUTE FUNCTION check_order_total();

    CREATE CONSTRAINT TRIGGER + DEFERRABLE 让事务末尾才校验，允许中间态不一致。

  2. 物化视图 + 业务读对账：定期跑一致性报表，发现不一致告警。

  3. 冗余字段 + 触发器自动维护：在 orders.total 上挂触发器，每次 items 变动重算 total，让数据库自己保证一致，外部应用层不用计算。

  4. 不放 total 字段：直接用视图实时算 SUM(price * qty)。代价是查询变重。

EXCLUDE 是另一类思路：很多看起来要跨行的「不重叠」问题，可以建模成 EXCLUDE 约束（如果能找到合适的操作符）。

与 MySQL 对比：MySQL 8.0.16 起才正式支持 CHECK 约束（之前版本静默忽略！），且限制类似。跨表逻辑同样靠触发器。

易错点：

  • 把 now() 写到 CHECK 里，PG 直接报错；MySQL 早期版本会静默接受但不生效，更危险。
  • 用「不属于约束的子查询 / 联表查询」也会被拒绝。

🔗 延伸阅读 ​

📌 下一章预告：第 5 章我们进入「高级查询」——JOIN 全家桶、窗口函数、CTE 与递归 CTE、GROUPING SETS / ROLLUP / CUBE、RETURNING、ON CONFLICT DO UPDATE（PG 的 upsert 神器）。SQL 写法会上一个大台阶。

🎬 可视化演示 ​

演示加载缓慢或样式异常？点此在新标签页打开 ↗

💻 示例代码 ​

python
"""
Ch4 配套代码 1 / 3 —— 6 大约束触发演示

依赖：先 init.sql 建好 ch4_* 系列表

演示：
  1. NOT NULL
  2. PRIMARY KEY 唯一
  3. UNIQUE（含 NULL 允许多个）
  4. CHECK（单列 + 跨列）
  5. FOREIGN KEY（含 ON DELETE RESTRICT / CASCADE）
  6. EXCLUDE USING gist（会议室时间段不重叠）
"""

import psycopg
from psycopg import errors as pe


DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"


def section(title: str) -> None:
    print("\n" + "=" * 64)
    print(title)
    print("=" * 64)


def expect_error(conn: psycopg.Connection, sql: str, params=None,
                 expect: type[Exception] = Exception, hint: str = "") -> None:
    """执行一个 *预期会失败* 的 SQL，捕获约束错误并打印第一行。"""
    try:
        with conn.cursor() as cur:
            cur.execute(sql, params or ())
            cur.fetchall() if cur.description else None
        print(f"  ⚠️  预期失败但通过了！SQL: {sql[:80]}")
    except expect as e:
        conn.rollback()
        msg = str(e).splitlines()[0]
        print(f"  ✅ {hint:38} -> {msg}")
    except Exception as e:
        conn.rollback()
        print(f"  ❌ 未预期错误类型: {type(e).__name__}: {e}")


def demo_not_null(conn: psycopg.Connection) -> None:
    section("Demo 1: NOT NULL")

    expect_error(conn,
        "INSERT INTO ch4_rooms (name, capacity) VALUES (NULL, 10)",
        expect=pe.NotNullViolation,
        hint="name = NULL"
    )


def demo_primary_key(conn: psycopg.Connection) -> None:
    section("Demo 2: PRIMARY KEY 唯一")

    expect_error(conn,
        "INSERT INTO ch4_rooms (id, name, capacity) VALUES (1, 'dup', 10)",
        expect=pe.UniqueViolation,
        hint="id = 1 (已存在)"
    )


def demo_unique(conn: psycopg.Connection) -> None:
    section("Demo 3: UNIQUE 与 NULL")

    with conn.cursor() as cur:
        # name 列是 UNIQUE
        expect_error(conn,
            "INSERT INTO ch4_rooms (name, capacity) VALUES ('A101', 10)",
            expect=pe.UniqueViolation,
            hint="name = 'A101' (已存在)"
        )

        # phone UNIQUE 列允许多 NULL
        cur.execute("INSERT INTO ch4_customers (name, email, phone) VALUES ('U1', 'u1@x.com', NULL) RETURNING id")
        i1 = cur.fetchone()[0]
        cur.execute("INSERT INTO ch4_customers (name, email, phone) VALUES ('U2', 'u2@x.com', NULL) RETURNING id")
        i2 = cur.fetchone()[0]
        print(f"  ✅ phone=NULL 可以插多条: id={i1}, id={i2}")
        cur.execute("DELETE FROM ch4_customers WHERE id IN (%s, %s)", (i1, i2))
        conn.commit()


def demo_check(conn: psycopg.Connection) -> None:
    section("Demo 4: CHECK 约束 (单列 + 跨列)")

    expect_error(conn,
        "INSERT INTO ch4_rooms (name, capacity) VALUES ('TooBig', 9999)",
        expect=pe.CheckViolation,
        hint="capacity > 500"
    )

    expect_error(conn,
        "INSERT INTO ch4_orders (customer_id, list_price, sale_price) VALUES (1, 100, 200)",
        expect=pe.CheckViolation,
        hint="sale_price > list_price"
    )

    expect_error(conn,
        "INSERT INTO ch4_customers (name, email) VALUES ('X', 'invalid-email')",
        expect=pe.CheckViolation,
        hint="email 格式不对"
    )

    expect_error(conn,
        "INSERT INTO ch4_orders (customer_id, list_price, sale_price, status) VALUES (1, 10, 5, 'WHATEVER')",
        expect=pe.CheckViolation,
        hint="status 非法枚举值"
    )


def demo_fk(conn: psycopg.Connection) -> None:
    section("Demo 5: 外键 FOREIGN KEY")

    expect_error(conn,
        "INSERT INTO ch4_orders (customer_id, list_price, sale_price) VALUES (99999, 10, 5)",
        expect=pe.ForeignKeyViolation,
        hint="customer_id 不存在"
    )

    # ON DELETE RESTRICT：删父表会失败
    expect_error(conn,
        "DELETE FROM ch4_customers WHERE id = 1",
        expect=pe.ForeignKeyViolation,
        hint="客户被订单引用，RESTRICT 拒绝删"
    )

    # 演示 ON DELETE CASCADE（rooms -> bookings）
    with conn.cursor() as cur:
        cur.execute("INSERT INTO ch4_rooms (name, capacity) VALUES ('TempRoom', 5) RETURNING id")
        rid = cur.fetchone()[0]
        cur.execute(
            "INSERT INTO ch4_bookings (room_id, booker, period) VALUES (%s, 'tmp', "
            "tstzrange('2026-05-01 09:00+08', '2026-05-01 11:00+08'))",
            (rid,)
        )
        conn.commit()
        cur.execute("SELECT count(*) FROM ch4_bookings WHERE room_id = %s", (rid,))
        before = cur.fetchone()[0]
        cur.execute("DELETE FROM ch4_rooms WHERE id = %s", (rid,))
        conn.commit()
        cur.execute("SELECT count(*) FROM ch4_bookings WHERE room_id = %s", (rid,))
        after = cur.fetchone()[0]
        print(f"  ✅ ON DELETE CASCADE: 删除房间前预订数={before}, 删除后={after}")


def demo_exclude(conn: psycopg.Connection) -> None:
    section("Demo 6: EXCLUDE 排他约束 (PG 独有！)")

    with conn.cursor() as cur:
        cur.execute("SELECT id FROM ch4_rooms WHERE name = 'A101'")
        room_id = cur.fetchone()[0]

        print("  现有 A101 预订：")
        cur.execute("""
            SELECT id, booker, period FROM ch4_bookings
            WHERE room_id = %s ORDER BY period
        """, (room_id,))
        for r in cur.fetchall():
            print(f"     id={r[0]}  booker={r[1]:8}  period={r[2]}")
        print()

    expect_error(conn,
        """INSERT INTO ch4_bookings (room_id, booker, period) VALUES
           (%s, 'Frank', tstzrange('2026-04-17 10:00+08', '2026-04-17 12:00+08'))""",
        params=(room_id,),
        expect=pe.ExclusionViolation,
        hint="A101 10:00~12:00 与 09:00~11:00 重叠"
    )

    expect_error(conn,
        """INSERT INTO ch4_bookings (room_id, booker, period) VALUES
           (%s, 'Frank', tstzrange('2026-04-17 14:00+08', '2026-04-17 16:00+08'))""",
        params=(room_id,),
        expect=pe.ExclusionViolation,
        hint="A101 14:00~16:00 与 13:00~15:00 重叠"
    )

    # 不冲突时间段应该成功
    with conn.cursor() as cur:
        cur.execute("""
            INSERT INTO ch4_bookings (room_id, booker, period) VALUES
            (%s, 'Frank', tstzrange('2026-04-17 19:00+08', '2026-04-17 21:00+08'))
            RETURNING id
        """, (room_id,))
        new_id = cur.fetchone()[0]
        conn.commit()
        print(f"  ✅ 不冲突时段(19:00~21:00) 写入成功 id={new_id}")
        cur.execute("DELETE FROM ch4_bookings WHERE id = %s", (new_id,))
        conn.commit()


def main() -> None:
    with psycopg.connect(DSN, autocommit=False) as conn:
        demo_not_null(conn)
        demo_primary_key(conn)
        demo_unique(conn)
        demo_check(conn)
        demo_fk(conn)
        demo_exclude(conn)


if __name__ == "__main__":
    try:
        main()
    except psycopg.OperationalError as e:
        print(f"❌ 连接 PostgreSQL 失败: {e}")
python
"""
Ch4 配套代码 2 / 3 —— 物化视图实战

依赖：先 init.sql 建好 ch4_sales / ch4_mv_daily_sales

演示：
  1. 普通视图 vs 物化视图查询耗时
  2. REFRESH MATERIALIZED VIEW (锁) vs REFRESH ... CONCURRENTLY (不锁读)
  3. 没有 UNIQUE 索引时 CONCURRENTLY 会报错
  4. 给物化视图加索引让查询走 Index Scan
"""

import time

import psycopg


DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"


def section(title: str) -> None:
    print("\n" + "=" * 64)
    print(title)
    print("=" * 64)


def time_query(conn: psycopg.Connection, sql: str, repeat: int = 3) -> float:
    """跑 N 次取平均，返回毫秒"""
    times = []
    with conn.cursor() as cur:
        for _ in range(repeat):
            t0 = time.perf_counter()
            cur.execute(sql)
            cur.fetchall()
            times.append((time.perf_counter() - t0) * 1000)
    return sum(times) / len(times)


def demo_view_vs_mv(conn: psycopg.Connection) -> None:
    section("Demo 1: 普通视图 vs 物化视图查询耗时")

    sql_view = "SELECT * FROM ch4_v_sales_by_region ORDER BY total DESC"
    sql_mv   = "SELECT region, SUM(total) AS total FROM ch4_mv_daily_sales GROUP BY region ORDER BY total DESC"

    t1 = time_query(conn, sql_view)
    t2 = time_query(conn, sql_mv)

    print(f"  普通视图   ch4_v_sales_by_region   平均 {t1:.2f} ms (每次都算 GROUP BY)")
    print(f"  物化视图   ch4_mv_daily_sales 聚合 平均 {t2:.2f} ms (查表 + 小聚合)")
    if t1 > 0:
        print(f"  💡 物化视图比普通视图快 {t1/t2:.1f} 倍 (基于 5 万行数据，越多差距越大)")


def demo_refresh_modes(conn: psycopg.Connection) -> None:
    section("Demo 2: REFRESH 两种模式")

    with conn.cursor() as cur:
        # 普通 REFRESH（锁 ACCESS EXCLUSIVE）
        t0 = time.perf_counter()
        cur.execute("REFRESH MATERIALIZED VIEW ch4_mv_daily_sales")
        full_ms = (time.perf_counter() - t0) * 1000
        print(f"  REFRESH MATERIALIZED VIEW (FULL):       {full_ms:.1f} ms (期间 SELECT 阻塞)")

        # CONCURRENTLY（不锁读）
        t0 = time.perf_counter()
        cur.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY ch4_mv_daily_sales")
        concurrent_ms = (time.perf_counter() - t0) * 1000
        print(f"  REFRESH MATERIALIZED VIEW CONCURRENTLY: {concurrent_ms:.1f} ms (期间 SELECT 不阻塞)")
        print(f"  💡 CONCURRENTLY 比 FULL 慢 (要做差异比对)，但生产几乎都用它")


def demo_concurrently_requires_unique(conn: psycopg.Connection) -> None:
    section("Demo 3: CONCURRENTLY 要求 UNIQUE 索引")

    with conn.cursor() as cur:
        # 临时创建一个没 UNIQUE 索引的物化视图
        cur.execute("DROP MATERIALIZED VIEW IF EXISTS ch4_mv_no_unique")
        cur.execute("CREATE MATERIALIZED VIEW ch4_mv_no_unique AS SELECT region, count(*) AS cnt FROM ch4_sales GROUP BY region")

        try:
            cur.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY ch4_mv_no_unique")
            print("  ⚠️ 未预期到通过！")
        except psycopg.errors.InvalidTableDefinition as e:
            conn.rollback()
            print(f"  ✅ 预期失败: {str(e).splitlines()[0]}")
            print("     -> 必须 CREATE UNIQUE INDEX 才能 CONCURRENTLY 刷新")

        cur.execute("DROP MATERIALIZED VIEW ch4_mv_no_unique")
        conn.commit()


def demo_explain(conn: psycopg.Connection) -> None:
    section("Demo 4: 物化视图查询的 EXPLAIN")

    with conn.cursor() as cur:
        cur.execute("""
            EXPLAIN (ANALYZE, BUFFERS)
            SELECT * FROM ch4_mv_daily_sales
            WHERE day = current_date - 1 AND region = 'North'
        """)
        print("  ↓ 物化视图按主键命中 Index Scan")
        for row in cur.fetchall():
            print(f"     {row[0]}")


def demo_data_freshness(conn: psycopg.Connection) -> None:
    section("Demo 5: 物化视图的「过期」演示")

    with conn.cursor() as cur:
        # 插入一行新销售记录
        cur.execute("""
            INSERT INTO ch4_sales (region, amount, sold_at)
            VALUES ('North', 9999.99, now())
            RETURNING id
        """)
        new_id = cur.fetchone()[0]
        conn.commit()

        cur.execute("SELECT count(*) FROM ch4_sales WHERE region = 'North'")
        live = cur.fetchone()[0]
        cur.execute("SELECT SUM(cnt) FROM ch4_mv_daily_sales WHERE region = 'North'")
        mv_cnt = cur.fetchone()[0]

        print(f"  写入新记录后：")
        print(f"     ch4_sales (实时)            North 总条数 = {live}")
        print(f"     ch4_mv_daily_sales (过期)   North 总条数 = {mv_cnt}    ← 还没刷新")

        cur.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY ch4_mv_daily_sales")
        cur.execute("SELECT SUM(cnt) FROM ch4_mv_daily_sales WHERE region = 'North'")
        mv_cnt2 = cur.fetchone()[0]
        print(f"     REFRESH 后                  North 总条数 = {mv_cnt2}    ← 同步上了")

        # 清理
        cur.execute("DELETE FROM ch4_sales WHERE id = %s", (new_id,))
        cur.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY ch4_mv_daily_sales")
        conn.commit()


def main() -> None:
    with psycopg.connect(DSN, autocommit=False) as conn:
        demo_view_vs_mv(conn)
        demo_refresh_modes(conn)
        demo_concurrently_requires_unique(conn)
        demo_explain(conn)
        demo_data_freshness(conn)


if __name__ == "__main__":
    try:
        main()
    except psycopg.OperationalError as e:
        print(f"❌ 连接 PostgreSQL 失败: {e}")
python
"""
Ch4 配套代码 3 / 3 —— SERIAL vs IDENTITY 完整对比

依赖：先 init.sql 建好 ch4_serial_demo / ch4_identity_always / ch4_identity_default

演示：
  1. SERIAL 允许直接塞 id（埋下后续主键冲突的雷）
  2. IDENTITY ALWAYS 拒绝直接塞 id
  3. OVERRIDING SYSTEM VALUE 强制覆盖
  4. 序列与 IDENTITY 的 nextval / setval / RESTART
  5. 事务回滚导致序列跳号（这是 PG 的设计取舍）
"""

import psycopg
from psycopg import errors as pe


DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"


def section(title: str) -> None:
    print("\n" + "=" * 64)
    print(title)
    print("=" * 64)


def demo_serial_can_force(conn: psycopg.Connection) -> None:
    section("Demo 1: SERIAL 列允许手工指定 id —— 危险！")

    with conn.cursor() as cur:
        cur.execute("TRUNCATE ch4_serial_demo RESTART IDENTITY")

        # 正常插入，让序列推进
        cur.execute("INSERT INTO ch4_serial_demo (name) VALUES ('row1') RETURNING id")
        print(f"  自动 id = {cur.fetchone()[0]}  (序列给的 1)")

        # 手工塞一个未来的 id —— SERIAL 不会拦
        cur.execute("INSERT INTO ch4_serial_demo (id, name) VALUES (10, 'manual') RETURNING id")
        print(f"  手工 id = {cur.fetchone()[0]}  (硬塞，序列没动！)")

        # 再正常插入，序列会从 2 开始 —— 但其实表里 id=2 没人用，没问题
        # 然后继续到 id=10 时才会爆炸
        cur.execute("INSERT INTO ch4_serial_demo (name) VALUES ('row3') RETURNING id")
        print(f"  自动 id = {cur.fetchone()[0]}  (序列给的 2，与手工塞的 10 暂时不冲突)")

        # 把序列设置为 9，模拟「快撞了」
        cur.execute("SELECT setval(pg_get_serial_sequence('ch4_serial_demo', 'id'), 9)")

        cur.execute("INSERT INTO ch4_serial_demo (name) VALUES ('row10') RETURNING id")
        print(f"  自动 id = {cur.fetchone()[0]}  (序列推进到 10)")

        try:
            cur.execute("INSERT INTO ch4_serial_demo (name) VALUES ('boom') RETURNING id")
            print(f"  ⚠️ 未爆炸: id = {cur.fetchone()[0]}")
        except pe.UniqueViolation as e:
            conn.rollback()
            print(f"  💥 主键冲突! {str(e).splitlines()[0]}")
            print("     -> SERIAL 让手工塞值与序列脱节，运维事故的常见根源")


def demo_identity_always_rejects(conn: psycopg.Connection) -> None:
    section("Demo 2: IDENTITY ALWAYS 拒绝手工塞 id —— 安全！")

    with conn.cursor() as cur:
        cur.execute("TRUNCATE ch4_identity_always RESTART IDENTITY")

        cur.execute("INSERT INTO ch4_identity_always (name) VALUES ('row1') RETURNING id")
        print(f"  自动 id = {cur.fetchone()[0]}")

        try:
            cur.execute("INSERT INTO ch4_identity_always (id, name) VALUES (100, 'manual')")
            print("  ⚠️ 未预期通过！")
        except pe.GeneratedAlways as e:
            conn.rollback()
            print(f"  ✅ 拒绝手工塞: {str(e).splitlines()[0]}")

        # OVERRIDING SYSTEM VALUE 强制覆盖（数据导入场景）
        cur.execute(
            "INSERT INTO ch4_identity_always (id, name) "
            "OVERRIDING SYSTEM VALUE VALUES (1000, 'imported') RETURNING id"
        )
        print(f"  ✅ OVERRIDING SYSTEM VALUE 强制塞: id = {cur.fetchone()[0]}")
        conn.commit()


def demo_identity_default(conn: psycopg.Connection) -> None:
    section("Demo 3: IDENTITY BY DEFAULT —— 行为接近 SERIAL")

    with conn.cursor() as cur:
        cur.execute("TRUNCATE ch4_identity_default RESTART IDENTITY")

        cur.execute("INSERT INTO ch4_identity_default (name) VALUES ('a') RETURNING id")
        print(f"  自动 id = {cur.fetchone()[0]}")
        cur.execute("INSERT INTO ch4_identity_default (id, name) VALUES (50, 'force') RETURNING id")
        print(f"  手工 id = {cur.fetchone()[0]}  (BY DEFAULT 允许)")
        conn.commit()


def demo_sequence_ops(conn: psycopg.Connection) -> None:
    section("Demo 4: 序列函数 nextval / currval / setval / lastval")

    with conn.cursor() as cur:
        cur.execute("SELECT nextval('ch4_order_no_seq')")
        a = cur.fetchone()[0]
        cur.execute("SELECT nextval('ch4_order_no_seq')")
        b = cur.fetchone()[0]
        cur.execute("SELECT currval('ch4_order_no_seq')")
        c = cur.fetchone()[0]
        cur.execute("SELECT lastval()")
        d = cur.fetchone()[0]
        print(f"  连续两次 nextval: {a}, {b}")
        print(f"  currval (本会话最近一次该序列): {c}")
        print(f"  lastval (本会话最近一次任意序列): {d}")

        cur.execute("SELECT setval('ch4_order_no_seq', 99000)")
        cur.execute("SELECT nextval('ch4_order_no_seq')")
        print(f"  setval 到 99000 后再 nextval: {cur.fetchone()[0]}")


def demo_rollback_skip(conn: psycopg.Connection) -> None:
    section("Demo 5: 事务回滚导致序列跳号（PG 设计取舍）")

    with conn.cursor() as cur:
        cur.execute("TRUNCATE ch4_identity_default RESTART IDENTITY")

        # 事务 1：成功
        cur.execute("INSERT INTO ch4_identity_default (name) VALUES ('keep') RETURNING id")
        print(f"  事务 1 成功插入 id = {cur.fetchone()[0]}")
        conn.commit()

        # 事务 2：插入再回滚 —— 但序列不会回退！
        cur.execute("INSERT INTO ch4_identity_default (name) VALUES ('rollback') RETURNING id")
        print(f"  事务 2 插入 id = {cur.fetchone()[0]}")
        conn.rollback()
        print("  事务 2 ROLLBACK")

        # 事务 3：再插入，看 id 是否跳号
        cur.execute("INSERT INTO ch4_identity_default (name) VALUES ('after') RETURNING id")
        print(f"  事务 3 插入 id = {cur.fetchone()[0]}    ← 跳过了被回滚的 id")
        conn.commit()

        print()
        print("  💡 这是 PG 的有意设计：nextval 不受事务保护，避免并发回滚")
        print("     互相阻塞。代价是 id 会跳号。如果需要严格连续号请用其他方案")
        print("     （如表锁 + max(id)+1，或订单号生成器）。")


def main() -> None:
    with psycopg.connect(DSN, autocommit=False) as conn:
        demo_serial_can_force(conn)
        demo_identity_always_rejects(conn)
        demo_identity_default(conn)
        demo_sequence_ops(conn)
        demo_rollback_skip(conn)


if __name__ == "__main__":
    try:
        main()
    except psycopg.OperationalError as e:
        print(f"❌ 连接 PostgreSQL 失败: {e}")
markdown
# 第 4 章 配套代码

> 演示 PG 的「数据质量守护者」：6 大约束（PK / UNIQUE / NOT NULL / CHECK / FK / EXCLUDE）、视图、物化视图、SERIAL vs IDENTITY。配套表统一以 `ch4_` 前缀，视图以 `ch4_v_` / `ch4_mv_` 前缀。

## 准备工作

1. 跑初始化脚本：
   ```bash
   psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql

EXCLUDE USING gist 依赖 btree_gist 扩展，脚本里已 CREATE EXTENSION IF NOT EXISTS btree_gist;

  1. 安装依赖：
    bash
    pip install "psycopg[binary]>=3.1"
  2. （可选）通过环境变量覆盖默认连接：
    bash
    export PG_DSN="host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"

脚本一览（推荐运行顺序） ​

脚本一句话说明关键 PG 特性
01_constraints.py6 大约束被故意触发 → 看真实报错信息PRIMARY KEY / UNIQUE / CHECK / FK ON DELETE / EXCLUDE USING gist / tstzrange
02_materialized_view.py普通视图 vs 物化视图查询性能 + REFRESH CONCURRENTLYCREATE MATERIALIZED VIEW / REFRESH … CONCURRENTLY / UNIQUE 索引前置条件
03_identity_serial.pySERIAL vs IDENTITY ALWAYS vs IDENTITY DEFAULT 行为差异GENERATED ALWAYS AS IDENTITY / OVERRIDING SYSTEM VALUE / pg_get_serial_sequence()

预期输出 ​

01_constraints.py 跑完会看到约 6 段「故意触发约束 → 抛错」的输出，关键片段：

[Demo 5] EXCLUDE 约束：同房间时段不能重叠
  尝试给 ch4_rooms[1] 再插一段 10:30-11:30 …
  ✅ 预期失败：conflicting key value violates exclusion constraint "no_overlap_in_room"
                Key (room_id, period)=(1, …) conflicts with existing key …

02_materialized_view.py 会对比同一个聚合的两种实现，常规输出：

[Demo 1] 普通视图   ch4_v_sales_by_region   平均 38.41 ms (每次都算 GROUP BY)
        物化视图   ch4_mv_daily_sales 聚合 平均 1.23 ms (查表 + 小聚合)
[Demo 2] REFRESH MATERIALIZED VIEW (FULL):       42.7 ms (期间 SELECT 阻塞)
        REFRESH MATERIALIZED VIEW CONCURRENTLY: 88.3 ms (期间 SELECT 不阻塞)

常见报错 ​

  • connection refused → PG 没起 / 端口不对
  • relation "ch4_rooms" does not exist → 没跑 ../init.sql
  • extension "btree_gist" is not available → 系统包未安装 postgresql-XX-btree-gist，先 apt install
  • cannot refresh materialized view "ch4_mv_no_unique" concurrently → CONCURRENTLY 必须有 UNIQUE 索引，这是脚本故意演示的失败场景
  • cannot insert a non-DEFAULT value into column "id" → 用 IDENTITY ALWAYS 时直接 INSERT id 会被禁止，需 OVERRIDING SYSTEM VALUE

01_constraints.py ↗ · 02_materialized_view.py ↗ · 03_identity_serial.py ↗ · README.md ↗