第 16 章:数据类型进阶 - PostgreSQL入门
在第 4 章，我们已经学习了像 INT, VARCHAR, DATE 这样的基本数据类型。它们就像是工具箱里的螺丝刀和扳手，能解决大部分日常问题。
但是，PostgreSQL 之所以强大，很大程度上因为它提供了一个极其丰富的“军火库”，里面有各种为特定场景量身打造的高级数据类型。用好它们，能极大地提升数据存储的效率、安全性和查询的便利性。
这一章，我们就来认识几位重量级选手：
- SERIAL: 实现自动增长的整数 ID。
- UUID: 全局唯一标识符，应对分布式系统的挑战。
- ARRAY: 在一个字段里存储多个值。
- JSON/JSONB: 在关系型数据库中优雅地处理半结构化数据。
- TIMESTAMPTZ: 带时区的时间戳，解决全球化应用的时间问题。
16.1SERIAL和BIGSERIAL：自增主键
在之前的例子中，我们为 id 列手动插入 1, 2, 3... 这样的值。这很麻烦，而且在多用户同时插入时，很容易产生冲突。
SERIAL 类型就是为了解决这个问题而生的。它并不是一个真正的数据类型，而是一个“语法糖”，一个快捷方式。
当你将一列定义为 SERIAL 时，PostgreSQL 会自动为你做三件事：
- 创建一个序列 (Sequence) 对象，这是一个专门用来生成不重复数字的计数器。
- 将列的数据类型设置为 INTEGER。
- 将列的默认值设置为从上面那个序列中获取下一个值。
使用方法:
CREATE TABLE products (
product_id SERIAL PRIMARY KEY, -- 看这里！
product_name VARCHAR(100) NOT NULL
);现在，当我们插入数据时，完全不需要为 product_id 提供值，数据库会自动为我们生成！
INSERT INTO products (product_name) VALUES ('笔记本电脑');
INSERT INTO products (product_name) VALUES ('机械键盘');
SELECT * FROM products;执行结果:
product_id | product_name
------------+--------------
1 | 笔记本电脑
2 | 机械键盘
(2 rows)product_id 被自动、安全地赋予了 1 和 2。
- SERIAL: 对应 INTEGER，最大值约 21 亿。对于绝大多数应用都够用了。
- BIGSERIAL: 对应 BIGINT，最大值约 9 百京（9 x 10^18）。如果你的表可能会有天文数字级别的行数（比如日志、事件记录），请使用 BIGSERIAL。
16.2UUID数据类型
SERIAL 生成的 ID 在单台数据库内是唯一的，但如果你的系统是分布式的，有多台数据库都需要生成 ID，SERIAL 就可能产生冲突。
UUID (Universally Unique Identifier) 就是为解决这个问题而生的。它是一个 128 位的数字，通常表示为 32 个十六进制数的字符串，比如
550e8400-e29b-41d4-a716-446655440000。
UUID 的值是通过算法生成的，它能保证在全世界的范围内，你生成的每一个 UUID 都是几乎不可能重复的。
使用方法:
首先，我们需要启用一个内置的扩展 uuid-ossp，它提供了生成 UUID 的函数。
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";然后，在建表时使用 UUID 类型，并设置默认值为 uuid_generate_v4() (生成版本 4 的随机 UUID)。
CREATE TABLE documents (
doc_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
title TEXT
);
INSERT INTO documents (title) VALUES ('第一份机密文件');
INSERT INTO documents (title) VALUES ('第二份机密文件');
SELECT * FROM documents;执行结果:
doc_id | title
------------------------------------+--------------------
a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d | 第一份机密文件
b6c7d8e9-f0a1-4b2c-8d9e-1f2a3b4c5d6e | 第二份机密文件
(2 rows)doc_id 被赋予了全局唯一的、无规律的 ID。
16.3ARRAY数组类型
有时候，我们需要在一个字段里存储一组同类型的值，比如一篇文章的多个标签、一个用户的多个电话号码。
传统做法是再建一张关联表（比如 post_tags 表），但这对于简单的场景来说有点“杀鸡用牛刀”。PostgreSQL 的 ARRAY 类型提供了一个更直接的解决方案。
你可以创建任何基础数据类型的数组，比如 INTEGER[], TEXT[], DATE[]。
使用方法:
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT,
tags TEXT[] -- 文本类型的数组
);
INSERT INTO articles (title, tags)
VALUES ('学习SQL', '{"sql", "database", "postgres"}');
INSERT INTO articles (title, tags)
VALUES ('学习Python', ARRAY['python', 'programming']); -- 另一种语法
SELECT * FROM articles;- 数组的值可以用 {} 大括号括起来的字符串表示，也可以用 ARRAY[...] 语法。
查询数组:
PostgreSQL 提供了一系列强大的函数和操作符来查询数组。
-- 找出所有包含 'sql' 标签的文章
SELECT title FROM articles WHERE 'sql' = ANY(tags);
-- 找出所有同时包含 'python' 和 'programming' 标签的文章
SELECT title FROM articles WHERE tags @> ARRAY['python', 'programming'];16.4JSON和JSONB：处理半结构化数据
这可能是 PostgreSQL 最强大的特性之一。它允许你在关系型数据库中，原生、高效地存储和查询 JSON 数据。
- JSON: 以纯文本形式存储 JSON 数据。它会检查 JSON 格式是否合法，但仅此而已。
- JSONB: 以二进制格式存储 JSON 数据。它在存入时会做一些预处理，比如去掉重复的键、排序键等。
JSON vs JSONB:
特性 | JSON | JSONB |
存储 | 纯文本，保留原始格式（空格、顺序、重复键） | 二进制，不保留格式，效率更高 |
写入速度 | 快 (因为只做格式检查) | 稍慢 (因为要解析和转换) |
查询速度 | 慢 (每次查询都要重新解析) | 极快 (因为可以被索引) |
索引支持 | 有限 | 非常强大 (支持 GIN 索引) |
黄金法则 ：除非你有特殊需求要保留 JSON 的原始文本格式，否则永远优先使用 JSONB！
使用方法:
CREATE TABLE user_profiles (
user_id INT PRIMARY KEY,
profile JSONB
);
INSERT INTO user_profiles (user_id, profile) VALUES
(1, '{
"name": "张三",
"contact": {
"email": "zhangsan@example.com",
"phones": ["138...", "139..."]
},
"interests": ["reading", "coding"]
}');
-- 查询 JSON 内部的字段
-- -> 操作符返回一个 JSON 对象，->> 操作符返回文本
SELECT
profile -> 'name' AS json_name, -- 返回 "张三" (带引号的JSON字符串)
profile ->> 'name' AS text_name, -- 返回 张三 (纯文本)
profile -> 'contact' ->> 'email' AS email -- 链式查询
FROM user_profiles
WHERE user_id = 1;
-- 查询 JSON 数组中的元素
SELECT profile -> 'interests' -> 0 FROM user_profiles; -- 返回 "reading"
-- 检查是否存在某个键
SELECT * FROM user_profiles WHERE profile ? 'name';16.5TIMESTAMPvsTIMESTAMPTZ(带时区)
- TIMESTAMP: TIMESTAMP WITHOUT TIME ZONE 的别名。它只记录日期和时间，比如 2023-10-27 10:00:00。它不包含任何时区信息。
- TIMESTAMPTZ: TIMESTAMP WITH TIME ZONE 的别名。这是 PostgreSQL 中处理时间最推荐的方式。
TIMESTAMPTZ 的工作方式非常聪明：
- 当你插入一个时间值时，它会根据你当前数据库会话的时区设置，将这个时间转换成 UTC (协调世界时) 进行存储。
- 当你查询这个值时，它又会根据你当前会话的时区设置，将存储的 UTC 时间转换回你本地的时区时间来显示。
这意味着，无论你的用户和服务器在世界哪个角落，TIMESTAMPTZ 存储的都是一个绝对的、无歧义的时间点。
黄金法则 ：只要你的应用需要处理不同时区的用户，或者服务器可能部署在不同时区，请始终使用 TIMESTAMPTZ！
本章小结
你已经为你的数据表装备上了一批精良的“特种武器”！
- 我们学会了用 SERIAL 来创建自增主键。
- 用 UUID 来应对分布式系统的唯一 ID 需求。
- 用 ARRAY 来方便地存储数组。
- 用强大的 JSONB 来拥抱半结构化数据。
- 用 TIMESTAMPTZ 来一劳永逸地解决时区问题。
为你的数据选择最合适的数据类型，是数据库设计的核心一环。一个好的设计，能让你的应用事半功倍。
从下一章开始，我们将进入第四部分：数据完整性与表设计。我们将学习如何通过各种“约束”来保证数据的质量和一致性，这是构建一个健壮、可靠的数据库系统的基石。我们下一章见！