主题
第 3 章 数据类型详解
学习目标：彻底搞懂 PostgreSQL 内置数据类型的「正确用法 + 存储原理 + 与 MySQL 的差别」。看完之后能回答：
char(n)/varchar(n)/text在 PG 里到底有没有性能差别？timestamp和timestamptz哪个才是「带时区」的？JSON和JSONB谁更快？为什么生产基本都用JSONB？- 为什么 PG 的数组、范围、网络类型让 MySQL 用户「真香」？
3.0 概览：PostgreSQL 类型系统的「三层货架」
PostgreSQL 的类型系统是它真正的护城河。不像 MySQL 只给你「数字 / 字符串 / 时间 / JSON」四个货架，PG 像一个超大型仓储市场：
┌──────────────────────────────────────────────────────────────────┐
│ PostgreSQL 数据类型大全 │
├──────────────────┬───────────────────────────────────────────────┤
│ ① 标准类型 │ 数值 / 字符 / 时间 / 布尔 │
│ （MySQL 也有） │ decimal(p,s) / varchar(n) / timestamp / bool │
├──────────────────┼───────────────────────────────────────────────┤
│ ② 现代类型 │ UUID / JSON / JSONB │
│ （MySQL 部分有） │ 生成 ID、文档存储 │
├──────────────────┼───────────────────────────────────────────────┤
│ ③ PG 特色类型 │ 数组 int[] / 范围 int4range / 枚举 ENUM │
│ （MySQL 没有） │ 网络 inet / 几何 point / 自定义复合类型 │
└──────────────────┴───────────────────────────────────────────────┘💡 为什么 PG 类型这么丰富？因为 PostgreSQL 是一个 ORDBMS（Object-Relational DBMS，对象-关系型数据库），从 80 年代 Berkeley 项目时期就把「自定义类型 / 继承 / 函数即一等公民」作为核心设计目标。MySQL 是纯 RDBMS，只把表做成行列。
📌 本章约定：
- SQL 关键字一律 大写，标识符（表名、列名、类型名）一律 小写。
- 所有 psql 演示前缀为
learn_pg=#，假定你已经psql -h 127.0.0.1 -U postgres -d learn_pg。- 所有示例用的库叫
learn_pg，建表前请先CREATE DATABASE learn_pg;。
3.1 数值类型
3.1.1 一图看懂全部数值类型
┌──────────────────┬──────────┬──────────────────────────────────┬─────────────────┐
│ 类型 │ 字节 │ 范围 / 精度 │ 典型用途 │
├──────────────────┼──────────┼──────────────────────────────────┼─────────────────┤
│ smallint │ 2 │ -32768 ~ 32767 │ 状态码、年龄 │
│ integer / int │ 4 │ ±21 亿 │ 通用整型 │
│ bigint │ 8 │ ±9.2 × 10¹⁸ │ 雪花 ID、累加器 │
│ decimal/numeric │ 变长 │ 任意精度，最多 131072 位整数部分 │ 金额、科学计算 │
│ real │ 4 │ ~7 位有效数字 │ 传感器、近似值 │
│ double precision │ 8 │ ~15 位有效数字 │ 科学计算 │
│ smallserial │ 2 │ 1 ~ 32767（自增） │ 小表自增主键 │
│ serial │ 4 │ 1 ~ 21 亿（自增） │ 传统自增主键 │
│ bigserial │ 8 │ 1 ~ 9.2 × 10¹⁸（自增） │ 大表自增主键 │
│ money │ 8 │ 受 lc_monetary 影响 │ ❌ 不推荐使用 │
└──────────────────┴──────────┴──────────────────────────────────┴─────────────────┘3.1.2 整型：smallint / integer / bigint
🍱 生活类比：把整数列想成不同尺寸的快递柜——
smallint：手机大小的迷你柜，便宜但放不了大件。integer：标准柜，能放鞋盒、文件，最常用。bigint：大件柜，专放电器、雪花 ID 这种「超大号」。
sql
CREATE TABLE products (
id bigserial PRIMARY KEY,
sku integer NOT NULL,
stock smallint DEFAULT 0,
sold bigint DEFAULT 0
);
INSERT INTO products (sku, stock, sold) VALUES (101, 50, 1234567890123);
SELECT * FROM products;实操输出：
learn_pg=# SELECT * FROM products;
id | sku | stock | sold
----+-----+-------+---------------
1 | 101 | 50 | 1234567890123
(1 row)📌 与 MySQL 的区别
- MySQL 有
TINYINT(1 字节)，PG 没有。需要 0/1 字段直接用boolean，需要小数字用smallint。- MySQL 的
INT(11)中的「11」是显示宽度（5.7 起已废弃）。PG 的integer没有宽度概念，写int(11)会报语法错误。- MySQL 的
UNSIGNED INT把范围扩到 0~42 亿。PG 没有 UNSIGNED，需要更大范围请直接用bigint。
3.1.3 任意精度：numeric / decimal
numeric(precision, scale) —— precision 是总位数，scale 是小数点后位数。decimal 是 numeric 的别名，完全等价。
💰 生活类比：
numeric像银行的精算尺，可以无限拉长；real/double像家用电子秤，精度有限会四舍五入。所有涉及钱的字段，必须用numeric。
sql
CREATE TABLE orders (
id bigserial PRIMARY KEY,
amount numeric(12, 2) NOT NULL, -- 最多 10 位整数 + 2 位小数
rate numeric NOT NULL -- 不限精度，能存任意大数
);
INSERT INTO orders (amount, rate) VALUES
(9999.99, 0.0001),
(1234567890.12, 1e30); -- rate 列能存 10³⁰
SELECT * FROM orders;learn_pg=# SELECT * FROM orders;
id | amount | rate
----+---------------+---------------------------------------
1 | 9999.99 | 0.0001
2 | 1234567890.12 | 1000000000000000000000000000000
(2 rows)精度陷阱实测：
sql
SELECT 0.1::real + 0.2::real; -- → 0.3 (但实际有精度损失)
SELECT 0.1::numeric + 0.2::numeric; -- → 0.3（精确）
SELECT 1.0::real / 3; -- → 0.33333334
SELECT 1.0::numeric / 3; -- → 0.33333333333333333333 (默认 20 位)3.1.4 浮点：real / double precision
real= 单精度（IEEE 754 32 位），约 6~7 位有效数字。double precision= 双精度（IEEE 754 64 位），约 15~17 位有效数字。
陷阱：浮点比较永远不要用 =！
sql
SELECT (0.1::real + 0.2::real) = 0.3::real; -- → false
SELECT abs((0.1::real + 0.2::real) - 0.3) < 1e-6; -- → true（正确做法）3.1.5 自增：smallserial / serial / bigserial
serial 不是真正的类型，而是**「整型 + 序列 + 默认值 + 非空」的语法糖**。
sql
CREATE TABLE t1 (id serial PRIMARY KEY, name text);
-- 等价于（PG 自动展开）：
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;📌 与 MySQL
AUTO_INCREMENT的区别
- MySQL 的自增计数器是表的一个属性，重启可能跳号。
- PG 的序列是独立对象，可以被多张表共享，也能脱离表存在（详见第 4 章）。
- PG 10+ 推荐用
IDENTITY列代替serial（更符合 SQL 标准、权限处理更干净），第 4 章会专门对比。
3.1.6 不推荐的类型：money
sql
SELECT '$1,234.56'::money;
-- → $1,234.56（输出格式受 lc_monetary 影响，跨地区会变样！）money 类型的值受当前会话的本地化设置影响，跨语言环境时容易出问题，生产中一律用 numeric(12,2) 替代。
3.2 字符类型
3.2.1 三种字符类型 + PG 的「神奇真相」
┌─────────────┬──────────────────────────────────────────────────────┐
│ 类型 │ 说明 │
├─────────────┼──────────────────────────────────────────────────────┤
│ char(n) │ 固定长度，不足补空格，超出报错 │
│ varchar(n) │ 变长，最大 n 个字符，超出报错 │
│ text │ 变长，无长度限制（实际上限 1 GB） │
└─────────────┴──────────────────────────────────────────────────────┘⚠️ PostgreSQL 三大字符类型在物理存储上几乎一样，性能差别基本可忽略。
text反而是 PG 官方推荐的默认选择。
来自官方文档的原话：
"There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column." ——https://www.postgresql.org/docs/current/datatype-character.html
3.2.2 实测三类型存储
sql
CREATE TABLE str_demo (
a char(10),
b varchar(10),
c text
);
INSERT INTO str_demo VALUES ('hi', 'hi', 'hi');
SELECT
a, length(a) AS a_len, octet_length(a) AS a_bytes,
b, length(b) AS b_len, octet_length(b) AS b_bytes,
c, length(c) AS c_len, octet_length(c) AS c_bytes
FROM str_demo; a | a_len | a_bytes | b | b_len | b_bytes | c | c_len | c_bytes
------------+-------+---------+----+-------+---------+----+-------+---------
hi | 10 | 10 | hi | 2 | 2 | hi | 2 | 2
(1 row)可以看到：
char(10)字段被右侧补到 10 个空格，浪费空间。varchar(10)和text完全一样，都是 2 字节。
📌 与 MySQL 的区别
维度 MySQL PostgreSQL char(n)存储固定占 n 字符 固定补空格到 n 字符 varchar(n)存储变长 + 1~2 字节长度前缀 变长 + 1~4 字节长度前缀 text不能创建索引（只能前缀索引），性能不如 varchar 和 varchar 完全一样，能直接索引 推荐选择 频繁查询用 varchar(255) 直接用 text，需要长度限制时再加 CHECK约束
3.2.3 推荐写法：用 text + check 替代 varchar(n)
sql
-- 不推荐（限制长度后想改，要 ALTER TABLE 锁表）：
CREATE TABLE u1 (name varchar(50));
-- 推荐（约束可独立修改）：
CREATE TABLE u2 (
name text NOT NULL,
CONSTRAINT name_len CHECK (length(name) BETWEEN 1 AND 50)
);3.2.4 TOAST：超长字符串去哪儿了？
text 字段超过约 2 KB 时，PG 会启动 TOAST（The Oversized-Attribute Storage Technique）：把大字段切片压缩，存到一个幕后 TOAST 表里，主表只保留一个指针。
┌───────────────┐ ┌────────────────────────┐
│ 主表 (heap) │ │ pg_toast.toast_xxx │
│ ┌───────────┐ │ │ ┌────────────────────┐ │
│ │ id │ name │ │ │ │ chunk_id │ chunk │ │
│ │ 1 │ ptr ─┼─┼──────► │ │ ... │ 数据片 │ │
│ └───────────┘ │ │ └────────────────────┘ │
└───────────────┘ └────────────────────────┘
「ptr」是 18 字节的 TOAST 指针，包含：
- 压缩后大小
- 原始大小
- TOAST 表 OID
- 值的 OID这就是为什么 PG 的 text 「无脑用」也不会拖慢小行查询：你不读那个长字段，就不用从 TOAST 表加载。
3.3 日期与时间类型
3.3.1 类型一览
┌──────────────────────────┬────────────────────────────────────────┐
│ 类型 │ 说明 │
├──────────────────────────┼────────────────────────────────────────┤
│ date │ 仅日期：'2026-04-17' │
│ time [(p)] │ 仅时间，不带时区：'14:30:15.123' │
│ time with time zone │ 带时区的时间（不推荐，几乎没人用） │
│ timestamp [(p)] │ 日期+时间，**不带时区** │
│ timestamptz / timestamp │ 日期+时间+时区，PG 强烈推荐！ │
│ with time zone │ │
│ interval │ 时间间隔：'1 day 02:00:00' │
└──────────────────────────┴────────────────────────────────────────┘3.3.2 timestamp vs timestamptz：教科书级别的天坑
🌍 生活类比：
timestamp（不带时区）= 一张照片，照片上印着「14:30」，但不知道是北京时间还是伦敦时间。给不同人看就是不同时刻。timestamptz（带时区）= 一张照片 + GPS 标签，无论谁拿到，都能算出在他自己时区的时刻。
实际存储：timestamptz 在磁盘上存的并不是「时间 + 时区字符串」，而是 UTC 的 8 字节整数。读取时根据当前会话的 TimeZone 参数动态转换显示。
sql
SET TimeZone = 'Asia/Shanghai';
CREATE TABLE ts_demo (
n serial PRIMARY KEY,
a timestamp, -- 不带时区
b timestamptz -- 带时区
);
INSERT INTO ts_demo (a, b) VALUES
('2026-04-17 14:00:00', '2026-04-17 14:00:00');
SELECT * FROM ts_demo; n | a | b
---+---------------------+-----------------------
1 | 2026-04-17 14:00:00 | 2026-04-17 14:00:00+08切到伦敦时区再看：
sql
SET TimeZone = 'Europe/London';
SELECT * FROM ts_demo; n | a | b
---+---------------------+-----------------------
1 | 2026-04-17 14:00:00 | 2026-04-17 07:00:00+01 ← 自动转换！可以看到：a 列「14:00」原封不动；b 列被自动转成伦敦时间「07:00」。
📌 与 MySQL 的区别
维度 MySQL PostgreSQL 不带时区 DATETIME（占 8 字节）timestamp（8 字节）带时区 TIMESTAMP（仅 4 字节，2038 问题）timestamptz（8 字节，无 2038 问题）默认 多数项目用 DATETIME官方强烈推荐 timestamptz时区存储 TIMESTAMP 把当前时区时间转 UTC 存储 timestamptz 同样存 UTC，行为类似
结论：业务表里需要「时刻」概念的字段，一律用 timestamptz。只有「日历日期」（生日、纪念日）这种没有时区意义的，才用 date 或 timestamp。
3.3.3 interval：时间间隔
sql
SELECT interval '1 year 2 months 3 days 4 hours';
-- → 1 year 2 mons 3 days 04:00:00
SELECT now() + interval '7 days'; -- 7 天后
SELECT now() - interval '1 hour 30 minutes'; -- 1.5 小时前
SELECT age(timestamp '1990-01-01', timestamp '2026-04-17');
-- → -36 years -3 mons -16 days
SELECT extract(epoch FROM interval '1 day'); -- → 86400📌 MySQL 用
DATE_ADD(now(), INTERVAL 7 DAY)，PG 直接now() + interval '7 days'，更接近自然语言。
3.3.4 时区处理黄金法则
┌──────────────────────────────────────────────────────────────┐
│ ① 业务表统一用 timestamptz │
│ ② 应用代码里**只传 UTC 时间或带时区的字符串** │
│ ③ 显示给用户时再按用户时区做转换（SET TimeZone） │
│ ④ 数据库参数 timezone 推荐设为 'UTC'，避免服务器迁移踩坑 │
└──────────────────────────────────────────────────────────────┘3.4 布尔类型
sql
CREATE TABLE flags (
id serial PRIMARY KEY,
is_active boolean DEFAULT true
);
-- PG 接受多种字面量
INSERT INTO flags (is_active) VALUES
(true), (false), (NULL),
('t'), ('f'), ('yes'), ('no'),
('1'), ('0'), ('on'), ('off');
SELECT * FROM flags; id | is_active
----+-----------
1 | t
2 | f
3 |
4 | t
5 | f
6 | t
7 | f
8 | t
9 | f
10 | t
11 | f📌 与 MySQL 的区别：MySQL 没有真正的 boolean，
BOOLEAN是TINYINT(1)的别名，存的是 0/1。PG 的boolean是真布尔类型，存储为 1 字节，支持三值逻辑（true / false / null）。
三值逻辑陷阱：
sql
SELECT true AND NULL; -- → NULL
SELECT false AND NULL; -- → false
SELECT true OR NULL; -- → true
SELECT NULL = NULL; -- → NULL（不是 true！）
SELECT NULL IS NULL; -- → true（这才是判断 NULL 的正确方法）3.5 UUID
UUID（Universally Unique Identifier）是 128 位的全局唯一标识符，PG 用 16 字节存储。
🆔 生活类比：UUID 像身份证号，全世界（理论上）唯一。两台机器同时生成 UUID 也几乎不会撞号，是分布式系统的「无中心化主键」。
sql
-- 启用扩展（PG 13+ 自带 gen_random_uuid，无需扩展）
-- CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE users (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name text NOT NULL
);
INSERT INTO users (name) VALUES ('Alice'), ('Bob');
SELECT * FROM users; id | name
--------------------------------------+-------
8b4d1c1e-2c3f-4a5b-9d6e-7f0a1b2c3d4e | Alice
1a2b3c4d-5e6f-7890-1234-56789abcdef0 | Bob手写 UUID：
sql
SELECT '8b4d1c1e-2c3f-4a5b-9d6e-7f0a1b2c3d4e'::uuid;
SELECT uuid_in('11111111-2222-3333-4444-555555555555');UUID vs bigserial vs 雪花 ID
┌───────────────┬────────────┬──────────────────┬──────────────────┐
│ 方案 │ 字节 │ 唯一性范围 │ 插入性能 │
├───────────────┼────────────┼──────────────────┼──────────────────┤
│ bigserial │ 8 │ 单库内 │ ✅ 最快 │
│ UUID v4 (随机) │ 16 │ 全球 │ ❌ 索引乱序写差 │
│ UUID v7 (时序) │ 16 │ 全球，按时间递增 │ ✅ 接近 bigserial│
│ 雪花 ID │ 8 │ 集群内 │ ✅ 快 │
└───────────────┴────────────┴──────────────────┴──────────────────┘💡 实战建议：
- 单库小项目 →
bigserial / IDENTITY。- 多服务、需要离线生成 ID →
UUID v7（PG 18 起内置uuidv7()）或雪花 ID。- 千万别给已有热点表的 PK 改成随机 UUID v4，B-Tree 索引会变成「随机插入地狱」，写入 IOPS 暴涨 5~10 倍。
3.6 数组类型（PG 招牌特色！）
3.6.1 一维数组
sql
CREATE TABLE posts (
id serial PRIMARY KEY,
title text NOT NULL,
tags text[], -- 字符串数组
likes int[] -- 整型数组
);
INSERT INTO posts (title, tags, likes) VALUES
('PG 入门', ARRAY['db', 'pg', 'tutorial'], ARRAY[100, 50, 200]),
('Redis 入门', '{cache, redis, tutorial}', '{80, 120}'),
('MySQL', ARRAY['db', 'mysql'], NULL);
SELECT * FROM posts; id | title | tags | likes
----+--------------+-------------------------+--------------
1 | PG 入门 | {db,pg,tutorial} | {100,50,200}
2 | Redis 入门 | {cache,redis,tutorial} | {80,120}
3 | MySQL | {db,mysql} |3.6.2 数组操作符
┌────────┬───────────────────────────────────────────────────────┐
│ 操作符 │ 含义 │
├────────┼───────────────────────────────────────────────────────┤
│ a[1] │ 下标访问（注意：从 1 开始，不是 0！） │
│ a[1:3] │ 切片 │
│ a || b │ 拼接两个数组 │
│ x = ANY(a) │ x 是否是 a 的元素 │
│ a @> b │ a 是否「包含」b（b 的所有元素都在 a 里） │
│ a <@ b │ a 是否「被包含于」b │
│ a && b │ a 和 b 是否有交集 │
│ array_length(a, 1) │ 数组长度（第 1 维） │
│ unnest(a) │ 把数组拆成多行 │
└────────┴───────────────────────────────────────────────────────┘实操：
sql
SELECT id, title, tags[1] AS first_tag
FROM posts; id | title | first_tag
----+--------------+-----------
1 | PG 入门 | db
2 | Redis 入门 | cache
3 | MySQL | dbsql
-- 找出包含 'tutorial' 标签的所有帖子
SELECT id, title FROM posts WHERE 'tutorial' = ANY(tags);
-- 找出标签同时包含 'db' 和 'pg' 的帖子
SELECT id, title FROM posts WHERE tags @> ARRAY['db', 'pg'];
-- 找出标签和 ['mysql','redis'] 有交集的帖子
SELECT id, title FROM posts WHERE tags && ARRAY['mysql', 'redis'];
-- 把 tags 拆成多行
SELECT id, unnest(tags) AS tag FROM posts; id | tag
----+----------
1 | db
1 | pg
1 | tutorial
2 | cache
2 | redis
2 | tutorial
3 | db
3 | mysql3.6.3 二维数组
sql
SELECT ARRAY[[1,2,3],[4,5,6]] AS m;
-- {{1,2,3},{4,5,6}}
SELECT (ARRAY[[1,2,3],[4,5,6]])[2][3]; -- → 63.6.4 GIN 索引加速数组查询
数组查询如果不加索引，会走全表扫。PG 提供 GIN（Generalized Inverted iNdex，倒排索引） 专门优化数组、JSONB、全文检索这类「多值字段」查询。
sql
CREATE INDEX idx_posts_tags ON posts USING gin (tags);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM posts WHERE tags @> ARRAY['pg']; QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on posts (cost=8.02..16.58 rows=1 width=64)
(actual time=0.012..0.013 rows=1 loops=1)
Recheck Cond: (tags @> '{pg}'::text[])
Heap Blocks: exact=1
Buffers: shared hit=4
-> Bitmap Index Scan on idx_posts_tags
(cost=0.00..8.02 rows=1 width=0)
(actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (tags @> '{pg}'::text[])
Buffers: shared hit=2
Planning Time: 0.073 ms
Execution Time: 0.030 ms📌 与 MySQL 的区别：MySQL 没有原生数组类型，要存 tag 列表通常用 JSON 字段或独立的关联表。PG 的数组让「一对多关系」的简单场景免去了建关联表的成本。
3.7 JSON / JSONB（PG 重点！）
3.7.1 JSON 与 JSONB 的差别
┌──────────────────┬──────────────────────────────────────┐
│ 对比项 │ json vs jsonb │
├──────────────────┼──────────────────────────────────────┤
│ 存储 │ 原始文本 | 解析后的二进制 │
│ 写入速度 │ 快 | 慢（要解析） │
│ 查询速度 │ 慢（每次重解析） | 快 │
│ 是否保留空格/顺序 │ 保留 | 不保留 │
│ 是否去重 key │ 不去重 | 去重（保留最后一个）│
│ 索引 │ ❌ 不支持 GIN | ✅ 支持 GIN │
│ 操作符 │ 少 | 多（含 @> ?） │
│ 推荐度 │ ⭐ | ⭐⭐⭐⭐⭐ │
└──────────────────┴──────────────────────────────────────┘🥡 生活类比：
json= 写在便签上的菜单（字符串），每次点菜都要重新读一遍。jsonb= 已经整理成结构化卡片的菜单，按菜名瞬间找到，但第一次整理稍慢。
99% 的场景用 jsonb。本节剩余示例都用 jsonb。
3.7.2 建表与写入
sql
CREATE TABLE events (
id bigserial PRIMARY KEY,
name text,
payload jsonb
);
INSERT INTO events (name, payload) VALUES
('login', '{"user_id": 1, "ip": "10.0.0.1", "device": {"os": "iOS", "ver": "17"}}'),
('order', '{"user_id": 1, "items": [{"sku": "A1", "qty": 2}, {"sku": "B3", "qty": 1}], "total": 199.5}'),
('logout', '{"user_id": 2, "ip": "10.0.0.2"}');
SELECT * FROM events;3.7.3 JSONB 操作符大全
┌──────────┬─────────────────────────────────────────────────┐
│ 操作符 │ 含义 │
├──────────┼─────────────────────────────────────────────────┤
│ -> │ 按 key/index 取「JSON 对象」 │
│ ->> │ 按 key/index 取「文本」 │
│ #> │ 按路径取「JSON 对象」（路径用 text[]） │
│ #>> │ 按路径取「文本」 │
│ @> │ 左侧 jsonb 是否「包含」右侧 │
│ <@ │ 左侧 jsonb 是否「被包含于」右侧 │
│ ? │ 顶层是否存在某个 key │
│ ?| │ 顶层是否存在数组中任一 key │
│ ?& │ 顶层是否存在数组中所有 key │
│ || │ 合并（同 key 后者覆盖前者） │
│ - │ 删除 key 或数组下标 │
│ #- │ 按路径删除 │
└──────────┴─────────────────────────────────────────────────┘实操：
sql
-- -> 与 ->>
SELECT
payload -> 'user_id' AS user_id_json, -- 1（jsonb 类型）
payload ->> 'user_id' AS user_id_text, -- "1"（text 类型）
payload -> 'device' AS device_json,
payload -> 'device' ->> 'os' AS os
FROM events WHERE name = 'login'; user_id_json | user_id_text | device_json | os
--------------+--------------+--------------------------+-----
1 | 1 | {"os":"iOS","ver":"17"} | iOSsql
-- #> 与 #>>
SELECT
payload #> '{device, os}' AS os_json,
payload #>> '{device, ver}' AS ver_text
FROM events WHERE name = 'login';sql
-- @> 包含查询（最常用！）
SELECT id, name FROM events
WHERE payload @> '{"user_id": 1}';
-- 找出所有 user_id 为 1 的事件
SELECT id, name FROM events
WHERE payload @> '{"items": [{"sku": "A1"}]}';
-- 找出 items 数组里包含 sku=A1 的订单sql
-- ? 是否存在 key
SELECT id, name FROM events WHERE payload ? 'items';
-- ?| 是否存在任意 key
SELECT id FROM events WHERE payload ?| ARRAY['items', 'device'];
-- ?& 是否存在所有 key
SELECT id FROM events WHERE payload ?& ARRAY['user_id', 'ip'];3.7.4 修改 JSONB
sql
-- jsonb_set(target, path, new_value, create_if_missing)
UPDATE events
SET payload = jsonb_set(payload, '{ip}', '"10.0.0.99"')
WHERE id = 1;
-- 添加新字段
UPDATE events
SET payload = payload || '{"channel": "web"}'::jsonb
WHERE id = 1;
-- 删除字段
UPDATE events
SET payload = payload - 'channel'
WHERE id = 1;
-- 嵌套修改
UPDATE events
SET payload = jsonb_set(payload, '{device, os}', '"Android"')
WHERE id = 1;
-- 数组追加（用 || 把另一个数组合并进去）
UPDATE events
SET payload = jsonb_set(
payload,
'{items}',
(payload -> 'items') || '[{"sku":"C9","qty":3}]'::jsonb
)
WHERE id = 2;3.7.5 JSON 路径语言（jsonpath，PG 12+）
sql
-- 取所有 sku
SELECT jsonb_path_query(payload, '$.items[*].sku') FROM events WHERE id = 2;
-- "A1"
-- "B3"
-- 取 qty > 1 的 sku
SELECT jsonb_path_query(payload, '$.items[*] ? (@.qty > 1)')
FROM events WHERE id = 2;
-- {"sku":"A1","qty":2}
-- 是否存在符合条件的元素
SELECT jsonb_path_exists(payload, '$.items[*] ? (@.qty >= 3)') FROM events WHERE id = 2;3.7.6 JSONB 的 GIN 索引
JSONB 字段如果要按内部 key 查询，必须建 GIN 索引，否则就是全表扫。
sql
-- 默认 jsonb_ops（支持所有操作符，但索引大）
CREATE INDEX idx_events_payload ON events USING gin (payload);
-- 精简版 jsonb_path_ops（只支持 @>，索引更小、更快）
CREATE INDEX idx_events_payload_path ON events USING gin (payload jsonb_path_ops);
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE payload @> '{"user_id": 1}'; QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on events (cost=12.01..28.51 rows=2 width=120)
(actual time=0.025..0.026 rows=2 loops=1)
Recheck Cond: (payload @> '{"user_id": 1}'::jsonb)
Heap Blocks: exact=1
Buffers: shared hit=5
-> Bitmap Index Scan on idx_events_payload_path
(cost=0.00..12.01 rows=2 width=0)
(actual time=0.018..0.018 rows=2 loops=1)
Index Cond: (payload @> '{"user_id": 1}'::jsonb)
Buffers: shared hit=2
Planning Time: 0.121 ms
Execution Time: 0.054 ms只想索引某个具体 key？用「表达式索引」更省空间：
sql
CREATE INDEX idx_events_user_id ON events ( (payload ->> 'user_id') );
-- 触发条件：必须用 ->> 写法
SELECT * FROM events WHERE payload ->> 'user_id' = '1';📌 与 MySQL JSON 的区别
维度 MySQL JSON PostgreSQL JSONB 存储 二进制（类似 jsonb） 二进制 索引 必须用「生成列 + 普通索引」 直接 GIN 索引整个字段 包含查询 JSON_CONTAINS(col, '{}')col @> '{}'（有索引）路径取值 col->>'$.a.b'col -> 'a' -> 'b'或col #>> '{a,b}'函数 30+ 个 JSON_*函数80+ 个 jsonb_*函数 + jsonpath性能 复杂查询不如 PG 业内公认的 JSON 之王
3.7.7 何时用 JSONB？何时不该用？
✅ 适合 JSONB 的场景：
- 字段不固定（多租户、灵活属性、第三方接口数据）
- 半结构化日志、事件、审计
- 文档型数据（用户偏好、商品规格）
❌ 不要用 JSONB 当万能容器：
- 查询频繁的字段拍出来做普通列（JSONB 取值再快也比直接列慢）
- 强约束的业务字段（NOT NULL、外键引用）
- 大量更新的字段（JSONB 是值替换，整字段重写）3.8 范围类型（PG 特色）
范围类型用一个值表示「有起止的区间」，对预订系统、版本号、价格区间等场景特别友好。
┌────────────────┬───────────────────────────────────────────┐
│ 类型 │ 对应基础类型 │
├────────────────┼───────────────────────────────────────────┤
│ int4range │ integer │
│ int8range │ bigint │
│ numrange │ numeric │
│ tsrange │ timestamp（不带时区） │
│ tstzrange │ timestamptz │
│ daterange │ date │
└────────────────┴───────────────────────────────────────────┘3.8.1 字面量
[a,b]闭区间(a,b)开区间[a,b)左闭右开（最推荐，能完美对接「下一段从 b 开始」）- 空集
'empty' - 无界
'(,b)'、'[a,)'
sql
SELECT '[1,5]'::int4range, '[1,5)'::int4range, '(,10)'::int4range; int4range | int4range | int4range
-----------+-----------+-----------
[1,6) | [1,5) | (,10)注意：
int4range是离散类型，PG 会自动把闭区间右端5标准化为开区间6。
3.8.2 操作符
┌──────────┬────────────────────────────────────┐
│ 操作符 │ 含义 │
├──────────┼────────────────────────────────────┤
│ @> │ 范围/元素 是否被包含在另一范围内 │
│ <@ │ 反向包含 │
│ && │ 两范围是否重叠 │
│ -|- │ 两范围是否「相邻」（无间隙不重叠） │
│ + │ 并集 │
│ * │ 交集 │
│ - │ 差集 │
│ lower(r)│ 下界 │
│ upper(r)│ 上界 │
└──────────┴────────────────────────────────────┘sql
SELECT '[1,10)'::int4range @> 5; -- → t
SELECT '[1,10)'::int4range && '[5,15)'::int4range; -- → t（重叠）
SELECT '[1,5)'::int4range -|- '[5,10)'::int4range; -- → t（相邻）
SELECT '[1,10)'::int4range * '[5,15)'::int4range; -- → [5,10)（交集）3.8.3 实战：会议室预订
sql
CREATE TABLE bookings (
id serial PRIMARY KEY,
room text,
period tstzrange
);
INSERT INTO bookings (room, period) VALUES
('A101', tstzrange('2026-04-17 09:00+08', '2026-04-17 11:00+08')),
('A101', tstzrange('2026-04-17 13:00+08', '2026-04-17 15:00+08'));
-- 找出 14:00~16:00 与已有预订冲突的房间
SELECT * FROM bookings
WHERE room = 'A101'
AND period && tstzrange('2026-04-17 14:00+08', '2026-04-17 16:00+08');第 4 章我们会用 EXCLUDE 排他约束 让「冲突」直接在数据库层被拒绝，比应用层判断更可靠。
3.9 枚举类型 ENUM
sql
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'done', 'cancelled');
CREATE TABLE orders2 (
id serial PRIMARY KEY,
status order_status NOT NULL DEFAULT 'pending'
);
INSERT INTO orders2 (status) VALUES ('pending'), ('paid'), ('cancelled');
-- 枚举值有内置顺序（按声明顺序）
SELECT * FROM orders2 WHERE status > 'paid';
-- shipped, done, cancelled新增枚举值：
sql
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'cancelled';
ALTER TYPE order_status ADD VALUE 'partial_refund' BEFORE 'done';⚠️ 枚举值不能删！ 只能
RENAME。所以业务方需要「软删」枚举值时，干脆别用 ENUM，改成「关联表 + check 约束」更灵活。
📌 与 MySQL 的区别：MySQL
ENUM('a','b')是列级别的，每张表单独定义，无法复用。PG 的 ENUM 是类型级别的，可在多张表/函数里共享。
3.10 自定义复合类型
PG 允许把多个字段组成一个「类型」，像 C 的 struct 一样使用。
sql
CREATE TYPE address AS (
street text,
city text,
zip text
);
CREATE TABLE customers (
id serial PRIMARY KEY,
name text,
home address,
work address
);
INSERT INTO customers (name, home, work) VALUES
('Alice', ROW('No.1 Main St', 'Beijing', '100000'),
ROW('No.99 Tech Park', 'Beijing', '100085'));
SELECT id, name, (home).city, (work).street FROM customers; id | name | city | street
----+-------+---------+-----------------
1 | Alice | Beijing | No.99 Tech Park注意访问字段时外层要加括号
(home).city，否则 PG 会把home.city当成「表别名 home 的列 city」。
3.11 网络地址类型（PG 特色）
sql
CREATE TABLE servers (
id serial PRIMARY KEY,
name text,
ip inet,
net cidr,
mac macaddr
);
INSERT INTO servers (name, ip, net, mac) VALUES
('app-1', '10.0.0.5/24', '10.0.0.0/24', '08:00:2b:01:02:03'),
('db-1', '192.168.1.10', '192.168.1.0/24', null);
-- 找出某 IP 属于哪个网段
SELECT * FROM servers WHERE inet '10.0.0.5' << net;
-- << 表示「严格属于」
-- 取主机部分
SELECT host(ip), masklen(ip), broadcast(net) FROM servers;inet 和 cidr 都能存 IPv4 和 IPv6，自带前缀长度，比用 varchar 存灵活百倍。
📌 MySQL 没有原生网络地址类型，要用
INT UNSIGNED + INET_ATON()函数手动转换，写起来繁琐且不支持 IPv6。
3.12 几何类型（简介）
point '(x, y)' 一个点
line '{A, B, C}' 无限长直线
lseg '[(x1,y1),(x2,y2)]' 线段
box '((x1,y1),(x2,y2))' 矩形
path 开/闭合折线
polygon 多边形
circle '<(x,y), r>' 圆sql
SELECT point '(1,2)' <-> point '(4,6)';
-- → 5 （两点间欧氏距离）
SELECT box '((0,0),(10,10))' @> point '(5,5)';
-- → t （矩形包含点）几何类型的强大版是 PostGIS 扩展（球面几何 + 索引），后续地理章节专门讲。
3.13 类型转换：CAST 与 ::
sql
SELECT CAST('100' AS integer); -- 标准 SQL 写法
SELECT '100'::integer; -- PG 简写，更常用
SELECT '2026-04-17'::date + 7; -- → 2026-04-24
SELECT 100::text || ' yuan'; -- → '100 yuan'
-- 不能转换的会报错
SELECT 'abc'::integer;
-- ERROR: invalid input syntax for type integer: "abc"3.14 本章小结
┌─────────────────────────────────────────────────────────────────┐
│ 本章核心要点 │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ① 钱用 numeric，时间用 timestamptz，字符串无脑用 text │
│ │
│ ② PG 的 char/varchar/text 物理上几乎一样，没有 MySQL 的差别 │
│ │
│ ③ JSONB 是 PG 的「招牌护城河」： │
│ - 二进制存储 + GIN 索引 + 80+ 函数 + jsonpath │
│ - @> ? -> ->> #> #>> jsonb_set jsonb_path_query │
│ │
│ ④ 数组、范围、枚举、网络、几何这些 PG 特色类型， │
│ 让很多原本要建关联表/写代码处理的场景一行 SQL 解决 │
│ │
│ ⑤ 时区处理：业务表统一用 timestamptz，服务器 timezone=UTC │
│ │
│ ⑥ 自增主键 PG 10+ 推荐 IDENTITY 替代 SERIAL（详见第 4 章） │
│ │
└─────────────────────────────────────────────────────────────────┘3.15 实操：跑一遍配套代码
实战代码见 03_data_types/code/：
01_numeric_string.py—— 数值/字符串类型 CRUD + numeric vs real 精度对比02_datetime_timezone.py—— 时间类型实测 + timestamp vs timestamptz 时区陷阱03_array_ops.py—— 数组读写、@> <@ && 操作符、unnest()、GIN 索引04_jsonb_demo.py—— JSONB 增删改查 + GIN 索引性能对比05_uuid_enum.py—— UUID 主键、ENUM 类型与扩展
启动前先 psql -h 127.0.0.1 -U postgres -f 03_data_types/init.sql。
浏览器演示见 03_data_types/demo.html：
- ① JSONB 操作符可视化沙盒（输入 JSON，点
->->>@>?看高亮命中） - ② 字符串类型 char vs varchar vs text 性能 benchmark 图表
- ③ 数组 + 范围 + 时间 类型互动 cheatsheet
🎮 配套演示
用浏览器打开
./03_data_types/demo.html，跟着可视化动画再走一遍本章核心概念。配套代码在
./03_data_types/code/，每个脚本都可以独立python xxx.py运行，先跑init.sql准备数据。
3.16 面试高频题
Q1：PostgreSQL 的 char(n) / varchar(n) / text 在底层有什么区别？应该怎么选？
考察点：对 PG 字符存储的理解，能否区分 PG 与 MySQL 的差异。
标准答案：
PG 三种字符类型在物理层面几乎完全一致，都用一个叫 varlena 的变长结构存储，超过 ~2 KB 触发 TOAST。性能差异基本可忽略。
具体差别：
char(n)：固定长度，写入不足 n 字符会右侧补空格到 n。读出时通常会把空格保留。最浪费空间。varchar(n)：变长，最大 n 字符。超出长度报错。除了「长度上限校验」外没有额外开销。text：变长，无长度限制（实际 1 GB）。完全无校验开销。
PostgreSQL 官方推荐用 text，需要长度限制时用 CHECK (length(col) <= 50)，比 varchar(50) 灵活——日后想改限制只要 ALTER TABLE ... DROP/ADD CONSTRAINT，不会锁全表重写。
与 MySQL 的关键区别：MySQL 的 text 不能直接做完整索引（只能前缀索引），且 varchar 在某些存储引擎下有更好的内联存储优化，所以 MySQL 圈推崇「能用 varchar 不用 text」。在 PG 这条经验完全不适用，照搬过来反而会导致很多不必要的 varchar(255) 出现在表结构里。
加分项：能讲清楚 TOAST 的触发条件（行大小 > TOAST_TUPLE_TARGET，默认 2 KB）和四种 TOAST 策略（PLAIN / EXTERNAL / EXTENDED / MAIN）。
Q2：timestamp 和 timestamptz 究竟有什么区别？为什么生产推荐 timestamptz？
考察点：时区处理的常见误区。
标准答案：
两者底层存储完全一样，都是 8 字节整数（自 2000-01-01 UTC 起的微秒数），区别在于「字面量解释规则」与「显示规则」：
timestamp（不带时区）：写入和读出完全不做时区转换。比如插入'2026-04-17 14:00'，读出还是'2026-04-17 14:00'。问题：不同时区的服务器/客户端读出来含义不一样，无法对应到一个具体时刻。timestamptz（带时区）：- 写入时若带时区（如
'2026-04-17 14:00+08'），转成 UTC 存。 - 写入时不带时区，按当前会话的
TimeZone参数解释，再转 UTC 存。 - 读出时按当前会话的
TimeZone显示。 - 同一行数据，北京会话看是「14:00+08」，伦敦会话看就是「07:00+01」。
- 写入时若带时区（如
为什么推荐 timestamptz：
- 业务里大多数时间字段表达的是「事件发生的真实时刻」，应该独立于客户端时区。
- 跨时区团队、跨地域部署时，不会因为某台机器的
TimeZone不同导致数据「错位」。 - 字节数和
timestamp完全一样（都是 8 字节），没有任何性能损失。
与 MySQL 的对比：MySQL 的 TIMESTAMP 才 4 字节，存的是 Unix 时间戳，所以有 2038 年 1 月 19 日溢出问题；MySQL 的 DATETIME 8 字节但不带时区。所以 MySQL 用户切到 PG 时常误以为 timestamp 等价 MySQL TIMESTAMP，要特别留意。
易错点：
- 应用层把
now()直接INSERT到timestamp字段，看起来正常，但服务器TimeZone一改就全乱。 - 数据库参数
timezone推荐统一设为UTC，让所有时区转换都在显式的客户端层完成。
Q3：JSON 和 JSONB 的区别？为什么生产基本都用 JSONB？
考察点：对 PG JSON 类型的设计选型理解。
标准答案：
| 维度 | json | jsonb |
|---|---|---|
| 存储格式 | 原始文本字符串 | 解析后的二进制结构 |
| 写入性能 | 快（不解析，原样存） | 略慢（要解析、规范化） |
| 查询性能 | 慢（每次访问要重新解析） | 快（直接按二进制索引） |
| 空格/key 顺序 | 完整保留 | 不保留（key 重新排序） |
| 重复 key | 全部保留 | 只保留最后一个 |
| 可索引 | ❌ 不能用 GIN | ✅ 支持 GIN（jsonb_ops / jsonb_path_ops） |
| 操作符数量 | 少 | 多（含 @> ? `? |
为什么生产推荐 JSONB：
- 可索引：业务里常需要「找 payload 里 user_id=1 的事件」，jsonb 配 GIN 索引能秒查。json 没办法，只能全表扫。
- 查询性能稳定：哪怕 json 内容很大，jsonb 也是 O(1) 路径访问；json 每次都要重解析整个文档。
- 函数生态丰富：
jsonb_set、jsonb_path_query、jsonb_strip_nulls等几十个内置函数都只对 jsonb 工作。
何时用 json（而非 jsonb）：
- 只是「把外部 JSON 字符串原封存档」，从不在数据库里查内部字段。
- 需要保留 key 重复或顺序信息（比如做严格的接口签名校验）。
加分项：
- jsonb_path_ops vs jsonb_ops 的区别：前者只支持
@>但索引体积小约 30%，写入快约 20%。 - 「只查某个 key」时用 表达式索引 更省：
CREATE INDEX ... ON t ((payload->>'user_id'))。
Q4：PostgreSQL 数组类型有什么用？应不应该用数组替代关联表？
考察点：选型权衡。
标准答案：
数组的合适场景：
- 数量小、几乎不变的标签集合：文章 tags、商品 specs。建 GIN 索引后
@>查询很快。 - 顺序有意义且不需要单独维护的列表：游戏胜局中各回合得分、问卷的多选答案。
- 避免单纯为「一对多」额外建关联表的小数据：用户的兴趣爱好、收藏的几个分类。
不该用数组的场景：
- 关联实体本身有更多属性：购物车里的「商品 + 数量 + 价格」一定要拆
cart_items表。 - 数组元素需要单独按 ID 引用（外键约束）：数据库不能给数组元素加外键。
- 数组会无限增长：超过几十个元素后，更新一个元素也要重写整个数组，性能会差。
- 跨业务复杂查询：JOIN、聚合、排序数组元素很麻烦，要
unnest后再处理。
实践口诀：「短小、整体读写、查询为主」用数组；「有自身属性、要按元素查改、会增长」用关联表。
与 MySQL 对比：MySQL 没有数组，等价方案要么用 JSON（查询能力受限），要么用关联表（额外 JOIN 开销）。PG 的数组让前一种场景更优雅，但不要把它当万能容器用，否则会失去关系型的优势。
加分项：能讲解 GIN 索引的工作原理（倒排索引：每个数组元素 → posting list of TIDs），以及 PG 14+ 的 gin_pending_list_limit 调优。
Q5：SERIAL 和 IDENTITY 有什么区别？为什么 PG 10+ 推荐用 IDENTITY？
考察点：SQL 标准合规性与 PG 现代化用法。
标准答案：
SERIAL 是伪类型，本质是「整型 + 序列 + DEFAULT 表达式」的语法糖：
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 t (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);两者关键区别：
| 维度 | SERIAL | IDENTITY |
|---|---|---|
| 标准合规 | ❌ 非标准 | ✅ SQL 标准 |
| 显式插入 | 总是允许，覆盖默认 | ALWAYS 拒绝（除非 OVERRIDING SYSTEM VALUE），BY DEFAULT 允许 |
| 序列权限 | 单独管理（容易遗漏） | 与列绑定，权限自动跟随 |
| 复制表 / 模板 | 序列对象需要单独处理 | 自动复制 |
DROP TABLE | 自动连带删序列 | 自动连带删序列 |
为什么推荐 IDENTITY：
- 拒绝「人为塞进自增列」可避免运维事故。比如
INSERT INTO t (id, ...) VALUES (1, ...)在 SERIAL 列是合法的，会跳过序列，导致后续自增冲突；而GENERATED ALWAYS AS IDENTITY会直接报错。 - 权限管理更干净，不会出现「给了 INSERT 权限但忘记给 sequence 的 USAGE」这种坑。
- 跨数据库迁移更友好（Oracle、DB2、SQL Server 都支持 IDENTITY 标准语法）。
与 MySQL AUTO_INCREMENT 对比：
- MySQL 的自增计数器是表的属性（存在
AUTO_INCREMENT元数据），重启可能跳号。 - PG 的序列是独立对象，重启不会跳号（但事务回滚时会跳号，因为
nextval不受事务保护——这是已知设计取舍）。
加分项：在第 4 章我们会讲 OWNED BY、setval()、序列与表解绑等高级用法。
Q6：为什么 PG 的索引能直接索引 text 字段，而 MySQL 不行？
考察点：跨数据库的存储原理对比。
标准答案：
MySQL（InnoDB）的限制：
- InnoDB 的索引键大小有上限（默认 3072 字节，配
innodb_large_prefix）。 TEXT/BLOB类型在 InnoDB 中存储为 off-page，索引只能基于「前缀」做：CREATE INDEX idx ON t (col(100))。- 这导致对长字符串的全值索引、唯一约束都做不了。
PostgreSQL 的优势：
- 索引键有 1/3 页大小（默认 2712 字节，可调）的限制，但这是键值大小，PG 会主动报错让你建表达式索引或部分索引，不会强制要前缀。
- 大字段（含
text）超阈值会自动 TOAST，但 TOAST 只影响数据存储，不影响索引能力。索引页只存索引键。 - 长 text 列做 B-Tree 索引可能因键过大失败，这时换 GIN 索引（全文检索
tsvector）或哈希索引（PG 10+ 持久化）即可。
实战建议：
- PG 中长 text 字段做精确匹配建议用：表达式索引
CREATE INDEX ... ON t (md5(col))；或CREATE INDEX ... USING hash (col)。 - MySQL 长字段索引必须前缀化，或者拆 hash 列。
加分项：能讲清楚 PG B-Tree 单条索引项默认上限是「页大小 / 3 ≈ 2712 字节」，以及 PG 13+ 的 B-Tree 去重（deduplication）特性能让重复键场景的索引体积缩小数倍。
🔗 延伸阅读
- 想用「质量守护者」管住数据完整性（PK / FK / CHECK / EXCLUDE / 视图 / 物化视图）？→ 第 4 章 约束与视图
- 想知道为什么 JSONB / 数组 / 全文检索都钦点 GIN 索引？→ 第 6 章 索引体系
- 想看 PG 自带的「向量类型」
vector（pgvector，做语义检索 / RAG）？→ 第 18 章 全文检索与向量
📌 下一章预告：第 4 章我们看 PostgreSQL 的「质量守护者」——主键、外键、唯一、检查、排他约束（PG 独有，会议室不冲突的关键），以及视图、物化视图、序列与 IDENTITY 的高级玩法。
🎬 可视化演示
演示加载缓慢或样式异常？点此在新标签页打开 ↗
💻 示例代码
python
"""
Ch3 配套代码 1 / 5 —— 数值与字符串类型实战
演示：
1. smallint / integer / bigint 各自的范围与溢出
2. numeric vs real 在金额计算上的精度差异
3. char(n) / varchar(n) / text 在 PG 里的存储与长度
4. 用 octet_length / pg_column_size 看真实占用
运行前：先执行 init.sql
psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql
"""
import psycopg
from decimal import Decimal
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_int_range(conn: psycopg.Connection) -> None:
section("Demo 1: 整型范围与溢出")
with conn.cursor() as cur:
for typ, value in [
("smallint", 32767),
("smallint", 32768), # 越界
("integer", 2_147_483_647),
("bigint", 9_223_372_036_854_775_807),
]:
try:
cur.execute(f"SELECT %s::{typ}", (value,))
got = cur.fetchone()[0]
print(f" ✅ {typ:9} <= {value:25} -> {got}")
except psycopg.errors.NumericValueOutOfRange as e:
conn.rollback()
print(f" ❌ {typ:9} <= {value:25} -> 溢出: {str(e).splitlines()[0]}")
def demo_numeric_vs_real(conn: psycopg.Connection) -> None:
section("Demo 2: numeric vs real —— 钱的计算请用 numeric")
with conn.cursor() as cur:
cur.execute("SELECT 0.1::real + 0.2::real, 0.1::numeric + 0.2::numeric")
r, n = cur.fetchone()
print(f" 0.1 + 0.2 real = {r!r}")
print(f" 0.1 + 0.2 numeric = {n!r}")
print()
cur.execute("SELECT 1.0::real / 3, 1.0::numeric / 3")
r, n = cur.fetchone()
print(f" 1.0 / 3 real = {r!r}")
print(f" 1.0 / 3 numeric = {n!r}")
print()
# 1 万次累加 0.01 元，看哪种类型保持精确
cur.execute("""
SELECT
SUM(0.01::real) AS sum_real,
SUM(0.01::numeric) AS sum_numeric
FROM generate_series(1, 100000)
""")
sr, sn = cur.fetchone()
print(f" 10 万次累加 0.01 (期望 1000):")
print(f" real = {sr!r} 误差 = {abs(float(sr) - 1000)}")
print(f" numeric = {sn!r} 误差 = {abs(Decimal(sn) - Decimal('1000'))}")
def demo_string_storage(conn: psycopg.Connection) -> None:
section("Demo 3: char(n) / varchar(n) / text 存储对比")
with conn.cursor() as cur:
cur.execute("DROP TABLE IF EXISTS demo_str")
cur.execute("""
CREATE TABLE demo_str (
a char(10),
b varchar(10),
c text
)
""")
cur.execute("INSERT INTO demo_str VALUES ('hi', 'hi', 'hi')")
cur.execute("""
SELECT
a, length(a), octet_length(a), pg_column_size(a),
b, length(b), octet_length(b), pg_column_size(b),
c, length(c), octet_length(c), pg_column_size(c)
FROM demo_str
""")
a, al, ab, ap, b, bl, bb, bp, c, cl, cb, cp = cur.fetchone()
print(f" char(10) value={a!r:14} length={al} octet_length={ab} pg_column_size={ap}")
print(f" varchar(10) value={b!r:14} length={bl} octet_length={bb} pg_column_size={bp}")
print(f" text value={c!r:14} length={cl} octet_length={cb} pg_column_size={cp}")
print()
print(" 💡 结论：char(10) 实际占 10 字节（被空格补齐），varchar/text 完全一样。")
cur.execute("DROP TABLE demo_str")
def demo_text_huge(conn: psycopg.Connection) -> None:
section("Demo 4: text 字段超大也能轻松塞 —— TOAST 在工作")
with conn.cursor() as cur:
cur.execute("DROP TABLE IF EXISTS demo_toast")
cur.execute("CREATE TABLE demo_toast (id int, big text)")
for size in (100, 1024, 10 * 1024, 100 * 1024, 1_000_000):
cur.execute(
"INSERT INTO demo_toast VALUES (%s, repeat('x', %s)) RETURNING pg_column_size(big)",
(size, size),
)
cs = cur.fetchone()[0]
print(f" 写入 {size:>9} 字节 text -> pg_column_size = {cs:>6} 字节 "
+ ("（已压缩 / TOAST）" if cs < size * 0.5 else ""))
cur.execute("DROP TABLE demo_toast")
def main() -> None:
with psycopg.connect(DSN, autocommit=True) as conn:
demo_int_range(conn)
demo_numeric_vs_real(conn)
demo_string_storage(conn)
demo_text_huge(conn)
if __name__ == "__main__":
try:
main()
except psycopg.OperationalError as e:
print(f"❌ 连接 PostgreSQL 失败: {e}")python
"""
Ch3 配套代码 2 / 5 —— 日期时间与时区陷阱
演示：
1. timestamp vs timestamptz 在不同时区会话下的表现
2. interval 的算术运算
3. age() / extract() / date_trunc() 常用函数
4. 把客户端 datetime 写入 timestamptz 的正确姿势
"""
from datetime import datetime, timezone, timedelta
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 demo_timestamp_vs_tz(conn: psycopg.Connection) -> None:
section("Demo 1: timestamp vs timestamptz —— 同一字面量，不同时区不同结果")
with conn.cursor() as cur:
cur.execute("DROP TABLE IF EXISTS demo_ts")
cur.execute("""
CREATE TABLE demo_ts (
n serial PRIMARY KEY,
a timestamp,
b timestamptz
)
""")
cur.execute("SET TimeZone = 'Asia/Shanghai'")
cur.execute(
"INSERT INTO demo_ts (a, b) VALUES (%s, %s)",
("2026-04-17 14:00:00", "2026-04-17 14:00:00"),
)
for tz in ("Asia/Shanghai", "Europe/London", "America/Los_Angeles"):
cur.execute(f"SET TimeZone = %s", (tz,))
cur.execute("SELECT a, b FROM demo_ts")
a, b = cur.fetchone()
print(f" 会话时区 = {tz:25} timestamp -> {a} timestamptz -> {b}")
print()
print(" 💡 timestamp 列原文不变；timestamptz 列被自动转换。")
cur.execute("DROP TABLE demo_ts")
def demo_interval(conn: psycopg.Connection) -> None:
section("Demo 2: interval 算术 —— 自然语言风格的时间运算")
with conn.cursor() as cur:
for sql in [
"SELECT now()",
"SELECT now() + interval '7 days'",
"SELECT now() - interval '1 hour 30 minutes'",
"SELECT date '2026-04-17' + 30", # date + integer = date
"SELECT age(timestamp '1990-01-01', timestamp '2026-04-17')",
"SELECT extract(epoch FROM interval '1 day')",
"SELECT extract(dow FROM timestamp '2026-04-17')", # 周几
"SELECT date_trunc('month', timestamp '2026-04-17 14:30')",
]:
cur.execute(sql)
print(f" {sql:62} -> {cur.fetchone()[0]}")
def demo_python_datetime(conn: psycopg.Connection) -> None:
section("Demo 3: 从 Python 写入 timestamptz —— 一定要带 tzinfo！")
with conn.cursor() as cur:
cur.execute("DROP TABLE IF EXISTS demo_pyts")
cur.execute("""
CREATE TABLE demo_pyts (
id serial primary key,
tag text,
ts timestamptz
)
""")
naive = datetime(2026, 4, 17, 14, 0, 0)
utc = datetime(2026, 4, 17, 14, 0, 0, tzinfo=timezone.utc)
sh = datetime(2026, 4, 17, 14, 0, 0, tzinfo=timezone(timedelta(hours=8)))
cur.execute("INSERT INTO demo_pyts (tag, ts) VALUES (%s, %s)", ("naive", naive))
cur.execute("INSERT INTO demo_pyts (tag, ts) VALUES (%s, %s)", ("UTC", utc))
cur.execute("INSERT INTO demo_pyts (tag, ts) VALUES (%s, %s)", ("+08", sh))
cur.execute("SET TimeZone = 'UTC'")
cur.execute("SELECT tag, ts FROM demo_pyts ORDER BY id")
print(" 以 UTC 会话时区读出：")
for tag, ts in cur.fetchall():
print(f" {tag:6} -> {ts}")
print("\n ⚠️ 'naive' datetime 被当作服务器本地时区解释，跨服务器迁移会踩坑！")
print(" ✅ 推荐：所有写入都用带 tzinfo 的 datetime，最简单的就是 datetime.now(timezone.utc)")
cur.execute("DROP TABLE demo_pyts")
def main() -> None:
with psycopg.connect(DSN, autocommit=True) as conn:
demo_timestamp_vs_tz(conn)
demo_interval(conn)
demo_python_datetime(conn)
if __name__ == "__main__":
try:
main()
except psycopg.OperationalError as e:
print(f"❌ 连接 PostgreSQL 失败: {e}")python
"""
Ch3 配套代码 3 / 5 —— 数组类型实战
演示：
1. 一维 / 二维数组的写入与读取（Python list 自动映射）
2. 操作符 = ANY / @> / <@ / && / -|-
3. unnest() 把数组拆成多行
4. 有无 GIN 索引时 @> 查询的执行计划对比
依赖：先 init.sql 建好 ch3_posts 表
"""
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 demo_basic_io(conn: psycopg.Connection) -> None:
section("Demo 1: Python list <-> PG 数组的自动映射")
with conn.cursor() as cur:
cur.execute("""
INSERT INTO ch3_posts (title, tags, likes)
VALUES (%s, %s, %s)
RETURNING id, title, tags, likes
""", ("Python 数组示例", ["pg", "python", "demo"], [1, 2, 3, 4, 5]))
row = cur.fetchone()
print(f" 写入后 RETURNING: id={row[0]} title={row[1]} tags={row[2]} likes={row[3]}")
print(f" Python 端类型: tags={type(row[2]).__name__} likes={type(row[3]).__name__}")
cur.execute("DELETE FROM ch3_posts WHERE id = %s", (row[0],))
def demo_operators(conn: psycopg.Connection) -> None:
section("Demo 2: 数组操作符全家桶")
with conn.cursor() as cur:
# = ANY
cur.execute("SELECT id, title FROM ch3_posts WHERE %s = ANY(tags) ORDER BY id", ("tutorial",))
print(" '\\'tutorial\\' = ANY(tags)' 命中:")
for r in cur.fetchall():
print(f" id={r[0]} title={r[1]}")
print()
# @> 包含
cur.execute("SELECT id, title FROM ch3_posts WHERE tags @> %s ORDER BY id", (["db", "pg"],))
print(" 'tags @> [db, pg]' 命中（必须同时含两个）:")
for r in cur.fetchall():
print(f" id={r[0]} title={r[1]}")
print()
# && 交集
cur.execute("SELECT id, title FROM ch3_posts WHERE tags && %s ORDER BY id", (["mysql", "redis"],))
print(" 'tags && [mysql, redis]' 命中（任一交集）:")
for r in cur.fetchall():
print(f" id={r[0]} title={r[1]}")
print()
# unnest
cur.execute("""
SELECT tag, COUNT(*) AS cnt
FROM ch3_posts, unnest(tags) AS tag
GROUP BY tag
ORDER BY cnt DESC, tag
""")
print(" unnest 后按 tag 统计文章数：")
for tag, cnt in cur.fetchall():
print(f" {tag:10} {cnt}")
def demo_2d_array(conn: psycopg.Connection) -> None:
section("Demo 3: 二维数组示例")
with conn.cursor() as cur:
cur.execute("SELECT %s::int[]", ([[1, 2, 3], [4, 5, 6]],))
m = cur.fetchone()[0]
print(f" Python list of list -> PG int[][] = {m}")
cur.execute("SELECT array_dims(%s::int[])", ([[1, 2, 3], [4, 5, 6]],))
print(f" array_dims = {cur.fetchone()[0]} -- [行下界:行上界][列下界:列上界]")
cur.execute("SELECT (ARRAY[[1,2,3],[4,5,6]])[2][3]")
print(f" 下标 [2][3] = {cur.fetchone()[0]} -- PG 数组下标默认从 1 开始！")
def demo_gin_plan(conn: psycopg.Connection) -> None:
section("Demo 4: GIN 索引下的 @> 查询计划")
with conn.cursor() as cur:
cur.execute("EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM ch3_posts WHERE tags @> ARRAY['pg']")
print(" ↓ 使用 GIN 索引的执行计划")
for line in cur.fetchall():
print(f" {line[0]}")
def main() -> None:
with psycopg.connect(DSN, autocommit=True) as conn:
demo_basic_io(conn)
demo_operators(conn)
demo_2d_array(conn)
demo_gin_plan(conn)
if __name__ == "__main__":
try:
main()
except psycopg.OperationalError as e:
print(f"❌ 连接 PostgreSQL 失败: {e}")python
"""
Ch3 配套代码 4 / 5 —— JSONB 实战
演示：
1. 写入 JSONB（Python dict 自动序列化）
2. -> ->> #> #>> 取值
3. @> ? ?| ?& 包含与 key 检测
4. jsonb_set / || / - 修改
5. jsonb_path_query —— PG 12+ 的 JSON 路径语言
6. 有 / 无 GIN 索引时的查询性能对比
依赖：先 init.sql 建好 ch3_events 表
"""
import json
import time
import psycopg
from psycopg.types.json import Jsonb
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_crud(conn: psycopg.Connection) -> None:
section("Demo 1: JSONB 增删改查 (CRUD)")
payload = {
"user_id": 99,
"ip": "10.0.0.99",
"device": {"os": "Linux", "ver": "6.7"},
"items": [{"sku": "X1", "qty": 10}, {"sku": "Y2", "qty": 1}],
}
with conn.cursor() as cur:
cur.execute(
"INSERT INTO ch3_events (name, payload) VALUES (%s, %s) RETURNING id",
("custom", Jsonb(payload)),
)
new_id = cur.fetchone()[0]
cur.execute("SELECT payload FROM ch3_events WHERE id = %s", (new_id,))
print(f" 原始 payload = {cur.fetchone()[0]}")
# -> 取「jsonb 子对象」 vs ->> 取文本
cur.execute("""
SELECT
payload -> 'user_id' AS user_id_jsonb,
payload ->> 'user_id' AS user_id_text,
payload #> '{device, os}' AS os_jsonb,
payload #>> '{device, ver}' AS ver_text,
payload -> 'items' -> 0 ->> 'sku' AS first_sku
FROM ch3_events WHERE id = %s
""", (new_id,))
for col, val in zip(
("user_id_jsonb", "user_id_text", "os_jsonb", "ver_text", "first_sku"),
cur.fetchone(),
):
print(f" {col:15} = {val!r} ({type(val).__name__})")
# 修改：jsonb_set 改字段；|| 合并；- 删除
cur.execute("""
UPDATE ch3_events
SET payload = jsonb_set(payload, '{device, os}', '"Windows"')
WHERE id = %s
""", (new_id,))
cur.execute("UPDATE ch3_events SET payload = payload || %s WHERE id = %s",
(Jsonb({"channel": "web"}), new_id))
cur.execute("UPDATE ch3_events SET payload = payload - 'ip' WHERE id = %s", (new_id,))
cur.execute("SELECT payload FROM ch3_events WHERE id = %s", (new_id,))
print(f"\n 修改后 payload = {cur.fetchone()[0]}")
cur.execute("DELETE FROM ch3_events WHERE id = %s", (new_id,))
def demo_existence_ops(conn: psycopg.Connection) -> None:
section("Demo 2: 存在性 / 包含查询")
with conn.cursor() as cur:
# @> 包含
cur.execute("SELECT id, name FROM ch3_events WHERE payload @> %s ORDER BY id",
(Jsonb({"user_id": 1}),))
print(" payload @> {user_id: 1}:")
for r in cur.fetchall():
print(f" id={r[0]} name={r[1]}")
print()
# ? 顶层 key 存在
cur.execute("SELECT id, name FROM ch3_events WHERE payload ? 'items' ORDER BY id")
print(" payload ? 'items':")
for r in cur.fetchall():
print(f" id={r[0]} name={r[1]}")
print()
# ?& 所有 key 都在
cur.execute("SELECT id, name FROM ch3_events WHERE payload ?& ARRAY['user_id', 'ip'] ORDER BY id")
print(" payload ?& [user_id, ip]:")
for r in cur.fetchall():
print(f" id={r[0]} name={r[1]}")
def demo_jsonpath(conn: psycopg.Connection) -> None:
section("Demo 3: jsonpath 查询 (PG 12+)")
with conn.cursor() as cur:
cur.execute("""
SELECT id, jsonb_path_query_array(payload, '$.items[*].sku') AS skus
FROM ch3_events
WHERE payload ? 'items'
ORDER BY id
""")
for r in cur.fetchall():
print(f" id={r[0]} 所有 sku = {r[1]}")
print()
cur.execute("""
SELECT id, jsonb_path_query_array(payload, '$.items[*] ? (@.qty >= 2)') AS heavy
FROM ch3_events
WHERE payload ? 'items'
ORDER BY id
""")
print(" qty >= 2 的 item：")
for r in cur.fetchall():
print(f" id={r[0]} -> {r[1]}")
def demo_index_perf(conn: psycopg.Connection) -> None:
section("Demo 4: GIN 索引性能对比 (5 万行)")
with conn.cursor() as cur:
cur.execute("DROP TABLE IF EXISTS bench_jsonb")
cur.execute("CREATE TABLE bench_jsonb (id bigserial PRIMARY KEY, data jsonb)")
# 灌 5 万行随机数据
cur.execute("""
INSERT INTO bench_jsonb (data)
SELECT jsonb_build_object(
'user_id', (random() * 1000)::int,
'kind', ('{login,order,view,share,like}'::text[])[1 + floor(random()*5)::int],
'val', (random() * 100)::int
)
FROM generate_series(1, 50000)
""")
# 不带索引
cur.execute("EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM bench_jsonb WHERE data @> %s",
(Jsonb({"user_id": 42}),))
no_idx = "\n".join(r[0] for r in cur.fetchall())
# 建 GIN 索引
cur.execute("CREATE INDEX idx_bench_data ON bench_jsonb USING gin (data jsonb_path_ops)")
cur.execute("ANALYZE bench_jsonb")
cur.execute("EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM bench_jsonb WHERE data @> %s",
(Jsonb({"user_id": 42}),))
with_idx = "\n".join(r[0] for r in cur.fetchall())
print(" ┌─ 无 GIN 索引 ────────────────────────────────")
for line in no_idx.split("\n"):
print(f" │ {line}")
print(" └─────────────────────────────────────────────")
print()
print(" ┌─ GIN 索引 (jsonb_path_ops) ──────────────────")
for line in with_idx.split("\n"):
print(f" │ {line}")
print(" └─────────────────────────────────────────────")
cur.execute("DROP TABLE bench_jsonb")
def main() -> None:
with psycopg.connect(DSN, autocommit=True) as conn:
demo_crud(conn)
demo_existence_ops(conn)
demo_jsonpath(conn)
demo_index_perf(conn)
if __name__ == "__main__":
try:
main()
except psycopg.OperationalError as e:
print(f"❌ 连接 PostgreSQL 失败: {e}")python
"""
Ch3 配套代码 5 / 5 —— UUID + ENUM + 复合类型 + 网络/范围类型综合演示
演示：
1. UUID 主键的生成与作为 PK 的写入
2. ENUM 类型的查询、添加新值
3. 自定义复合类型（ROW）的写入与字段访问
4. inet / cidr 的网段判断
5. tstzrange 的重叠 / 包含 / 相邻判断（为第 4 章 EXCLUDE 约束做铺垫）
"""
import uuid
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 demo_uuid(conn: psycopg.Connection) -> None:
section("Demo 1: UUID 主键")
with conn.cursor() as cur:
# 服务端生成
cur.execute("INSERT INTO ch3_users (name) VALUES (%s) RETURNING id", ("Server-UUID",))
sid = cur.fetchone()[0]
print(f" PG 生成的 UUID: {sid} type={type(sid).__name__}")
# 客户端生成（兼容性最好，不依赖 PG 函数）
my_id = uuid.uuid4()
cur.execute("INSERT INTO ch3_users (id, name) VALUES (%s, %s)", (my_id, "Client-UUID"))
cur.execute("SELECT id, name FROM ch3_users WHERE id = %s", (my_id,))
row = cur.fetchone()
print(f" Python 生成的 UUID: {row[0]} -> 写入成功 name={row[1]}")
cur.execute("DELETE FROM ch3_users WHERE id IN (%s, %s)", (sid, my_id))
def demo_enum(conn: psycopg.Connection) -> None:
section("Demo 2: ENUM 类型 —— 内置顺序与新增值")
with conn.cursor() as cur:
cur.execute("SELECT * FROM ch3_orders_enum ORDER BY id")
print(" 现有数据：")
for r in cur.fetchall():
print(f" id={r[0]} status={r[1]} note={r[2]}")
print()
# 枚举值有内置顺序（按声明顺序），可以直接比较！
cur.execute("""
SELECT id, status FROM ch3_orders_enum
WHERE status > 'paid'::ch3_order_status
ORDER BY status
""")
print(" status > 'paid' 的订单（按枚举声明顺序比较）：")
for r in cur.fetchall():
print(f" id={r[0]} status={r[1]}")
print()
# 试着插入非枚举值（会报错）
try:
cur.execute("INSERT INTO ch3_orders_enum (status) VALUES ('REFUNDED')")
except psycopg.errors.InvalidTextRepresentation as e:
conn.rollback()
print(f" ❌ 写入非枚举值被拒绝: {str(e).splitlines()[0]}")
# 添加新枚举值（必须在事务外执行）
try:
cur.execute("ALTER TYPE ch3_order_status ADD VALUE IF NOT EXISTS 'refunded' AFTER 'cancelled'")
print(" ✅ 已新增枚举值 'refunded'")
except Exception as e:
print(f" ⚠️ 添加枚举值: {e}")
def demo_composite(conn: psycopg.Connection) -> None:
section("Demo 3: 自定义复合类型（ROW）")
with conn.cursor() as cur:
cur.execute("SELECT id, name, (home).street, (home).city, (home).zip FROM ch3_customers ORDER BY id")
print(" 访问复合字段子项必须加括号 (home).city：")
for r in cur.fetchall():
print(f" id={r[0]} name={r[1]} street={r[2]} city={r[3]} zip={r[4]}")
def demo_inet(conn: psycopg.Connection) -> None:
section("Demo 4: inet / cidr 网段判断")
with conn.cursor() as cur:
cur.execute("""
SELECT name, ip, net,
ip << net AS strictly_in_net,
host(ip) AS host,
masklen(ip) AS prefix
FROM ch3_servers
ORDER BY id
""")
print(f" {'name':<8}{'ip':<22}{'net':<22}{'in_net':<8}host prefix")
for r in cur.fetchall():
print(f" {r[0]:<8}{str(r[1]):<22}{str(r[2]):<22}{str(r[3]):<8}{r[4]:<11}{r[5]}")
def demo_range(conn: psycopg.Connection) -> None:
section("Demo 5: tstzrange 重叠 / 包含 / 相邻 (为第 4 章 EXCLUDE 约束铺垫)")
with conn.cursor() as cur:
# 找出和 14:00~16:00 冲突的预订
cur.execute("""
SELECT id, room, period
FROM ch3_bookings_demo
WHERE room = 'A101'
AND period && tstzrange('2026-04-17 14:00+08', '2026-04-17 16:00+08')
ORDER BY id
""")
print(" 与 14:00~16:00 重叠的 A101 预订：")
rows = cur.fetchall()
if not rows:
print(" （无重叠）")
for r in rows:
print(f" id={r[0]} room={r[1]} period={r[2]}")
print()
cur.execute("""
SELECT
tstzrange('2026-04-17 09:00+08', '2026-04-17 11:00+08')
-|- tstzrange('2026-04-17 11:00+08', '2026-04-17 13:00+08') AS adjacent,
tstzrange('2026-04-17 09:00+08', '2026-04-17 11:00+08')
@> '2026-04-17 10:00+08'::timestamptz AS contains_10am
""")
adj, contains = cur.fetchone()
print(f" 9~11 -|- 11~13 (相邻无重叠) = {adj}")
print(f" 9~11 @> 10:00 = {contains}")
def main() -> None:
with psycopg.connect(DSN, autocommit=True) as conn:
demo_uuid(conn)
demo_enum(conn)
demo_composite(conn)
demo_inet(conn)
demo_range(conn)
if __name__ == "__main__":
try:
main()
except psycopg.OperationalError as e:
print(f"❌ 连接 PostgreSQL 失败: {e}")markdown
# 第 3 章 配套代码
> 演示 PG 类型系统：数值 / 字符串 / 日期时间 / 数组 / JSONB / UUID / ENUM / 复合 / 网络地址。配套表统一以 `ch3_` 前缀命名，避免与其他章节冲突。
## 准备工作
1. 跑初始化脚本：
```bash
psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql- 安装依赖：bash
pip install "psycopg[binary]>=3.1" - （可选）通过环境变量覆盖默认连接：bash
export PG_DSN="host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
脚本一览（推荐运行顺序）
| 脚本 | 一句话说明 | 关键 PG 特性 |
|---|---|---|
01_numeric_string.py | 数值 / 字符串 CRUD + numeric vs real 精度对比 | numeric(p,s) / real / text / length() / position() |
02_datetime_timezone.py | 时区类型实测、timestamp vs timestamptz 陷阱 | timestamptz / interval / AT TIME ZONE / date_trunc() |
03_array_ops.py | 数组读写 + 操作符 + GIN 索引 | TEXT[] / @> <@ && / unnest() / GIN |
04_jsonb_demo.py | JSONB 增删改查 + 索引性能对比 | jsonb_set() / -> ->> @> ? / GIN jsonb_path_ops |
05_uuid_enum.py | UUID 主键 + ENUM 类型扩展 | gen_random_uuid() / CREATE TYPE ... AS ENUM / 复合类型 / inet |
预期输出
04_jsonb_demo.py 跑完会看到 GIN 索引前后扫描方式的对比，关键片段：
[Demo 4] 给 ch3_orders.payload 建 GIN(jsonb_path_ops) 后查询：
EXPLAIN: Bitmap Heap Scan on ch3_orders (cost=...)
Recheck Cond: (payload @> '{"channel":"app"}'::jsonb)
-> Bitmap Index Scan on idx_ch3_orders_payload_gin
Index Cond: (payload @> '{"channel":"app"}'::jsonb)
耗时：1.234 ms vs 无索引时 102.456 ms常见报错
connection refused→ PG 没起 / 端口不对relation "ch3_orders" does not exist→ 没跑../init.sqlfunction gen_random_uuid() does not exist→ 缺pgcrypto扩展，执行CREATE EXTENSION pgcrypto;（PG 13+ 已内建该函数）type "ch3_order_status" does not exist→ 同样是没跑../init.sql
01_numeric_string.py ↗ · 02_datetime_timezone.py ↗ · 03_array_ops.py ↗ · 04_jsonb_demo.py ↗ · 05_uuid_enum.py ↗ · README.md ↗