在第 4 章，我们已经学习了像 INT, VARCHAR, DATE 这样的基本数据类型。它们就像是工具箱里的螺丝刀和扳手，能解决大部分日常问题。

但是，PostgreSQL 之所以强大，很大程度上因为它提供了一个极其丰富的“军火库”，里面有各种为特定场景量身打造的高级数据类型。用好它们，能极大地提升数据存储的效率、安全性和查询的便利性。

这一章，我们就来认识几位重量级选手：

• SERIAL: 实现自动增长的整数 ID。
• UUID: 全局唯一标识符，应对分布式系统的挑战。
• ARRAY: 在一个字段里存储多个值。
• JSON/JSONB: 在关系型数据库中优雅地处理半结构化数据。
• TIMESTAMPTZ: 带时区的时间戳，解决全球化应用的时间问题。

16.1SERIAL和BIGSERIAL：自增主键

在之前的例子中，我们为 id 列手动插入 1, 2, 3... 这样的值。这很麻烦，而且在多用户同时插入时，很容易产生冲突。

SERIAL 类型就是为了解决这个问题而生的。它并不是一个真正的数据类型，而是一个“语法糖”，一个快捷方式。

当你将一列定义为 SERIAL 时，PostgreSQL 会自动为你做三件事：

1. 创建一个序列 (Sequence) 对象，这是一个专门用来生成不重复数字的计数器。
2. 将列的数据类型设置为 INTEGER。
3. 将列的默认值设置为从上面那个序列中获取下一个值。

使用方法:

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！

使用方法:

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 的工作方式非常聪明：

1. 当你插入一个时间值时，它会根据你当前数据库会话的时区设置，将这个时间转换成 UTC (协调世界时) 进行存储。
2. 当你查询这个值时，它又会根据你当前会话的时区设置，将存储的 UTC 时间转换回你本地的时区时间来显示。

这意味着，无论你的用户和服务器在世界哪个角落，TIMESTAMPTZ 存储的都是一个绝对的、无歧义的时间点。

黄金法则 ：只要你的应用需要处理不同时区的用户，或者服务器可能部署在不同时区，请始终使用 TIMESTAMPTZ！

本章小结

你已经为你的数据表装备上了一批精良的“特种武器”！

• 我们学会了用 SERIAL 来创建自增主键。
• 用 UUID 来应对分布式系统的唯一 ID 需求。
• 用 ARRAY 来方便地存储数组。
• 用强大的 JSONB 来拥抱半结构化数据。
• 用 TIMESTAMPTZ 来一劳永逸地解决时区问题。

为你的数据选择最合适的数据类型，是数据库设计的核心一环。一个好的设计，能让你的应用事半功倍。

从下一章开始，我们将进入第四部分：数据完整性与表设计。我们将学习如何通过各种“约束”来保证数据的质量和一致性，这是构建一个健壮、可靠的数据库系统的基石。我们下一章见！