PostgreSQL 数据类型

这一课你将能做到

1. 为练习表选用 UUID、JSONB、TEXT[]、TIMESTAMPTZ 四类高频类型并完成增删改查
2. 说明为何业务库优先 JSONB 而非 JSON，以及数组适合什么、不适合什么
3. 对照本站 MySQL 的整型主键 / DATETIME 习惯，写出「何时仍用 BIGINT 主键」的判断

为什么要学

关系型不只是 INT + VARCHAR。PostgreSQL 内置类型丰富：UUID 适合对外暴露的标识，JSONB 存半结构化配置，数组存简单多值，timestamptz 正确处理时区。不会选类型，后面索引与 API 映射都会别扭。

本课聚焦四类日常高频；JSONB 的运算符与 GIN 索引在下一课展开。官方类型一览见 PostgreSQL 文档 Data Types 章。

先建立图像

一行 cf_track 可以长这样（概念上）：

 id          BIGINT / UUID     ← 谁唯一认出这一行
 slug        VARCHAR           ← 给人看的稳定键
 metadata    JSONB             ← 扩展袋：标签、开关、统计
 tags        TEXT[]            ← 简单多值（也可用 JSONB 数组）
 created_at  TIMESTAMPTZ       ← 「绝对时间」+ 展示时换区

类型	人话	典型用途
UUID	全球唯一的 128 位标识	API key、分布式 ID
JSONB	可索引的二进制 JSON	扩展字段、配置、嵌套文档
JSON	保留文本形态的 JSON	几乎总改选 JSONB
TEXT[]	文本数组	标签、简单枚举多选
TIMESTAMPTZ	带时区的时间戳	创建/更新时间
TIMESTAMP	无时区	易踩坑，业务库少用

跟着做

步骤 1：准备扩展与 schema

docker exec -it pg-dev psql -U codeforge -d codeforge_pg

CREATE SCHEMA IF NOT EXISTS app;

-- gen_random_uuid() 在较新版本可用；pgcrypto 提供加密相关函数
CREATE EXTENSION IF NOT EXISTS pgcrypto;

若 gen_random_uuid() 已存在于核心，扩展仍常装，便于文档与旧库一致。

步骤 2：UUID 主键表示例

CREATE TABLE IF NOT EXISTS app.api_key (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- 插入时自动生成
    name       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO app.api_key (name) VALUES ('local-dev');
SELECT id, name, created_at FROM app.api_key;

应看到一串 UUID。注意：本站 cf_track.id 仍是 BIGINT——OLTP 自增主键很常见；UUID 适合「不可猜测、跨系统合并」场景，不要教条「全部改 UUID」。

步骤 3：给课程表加 JSONB

若尚无 app.cf_track，先按 pg-ddl 建表，再执行：

ALTER TABLE app.cf_track
  ADD COLUMN IF NOT EXISTS metadata JSONB NOT NULL DEFAULT '{}'::jsonb;

UPDATE app.cf_track
SET metadata = '{"tags": ["sql", "backend"], "featured": true}'::jsonb
WHERE slug = 'sql-essentials';

UPDATE app.cf_track
SET metadata = '{"tags": ["sql", "pg"], "featured": true, "stats": {"views": 100}}'::jsonb
WHERE slug = 'postgresql-essentials';

-- 预习：取出文本标量
SELECT slug, metadata ->> 'featured' AS featured_text
FROM app.cf_track;

步骤 4：数组列

CREATE TABLE IF NOT EXISTS app.lesson_tag (
    lesson_id BIGINT PRIMARY KEY,
    tags      TEXT[] NOT NULL DEFAULT '{}'
);

INSERT INTO app.lesson_tag (lesson_id, tags)
VALUES
  (1, ARRAY['ddl', 'constraints']),
  (2, ARRAY['jsonb', 'index']);

-- 包含某标签
SELECT lesson_id, tags
FROM app.lesson_tag
WHERE 'ddl' = ANY (tags);

数组适合「扁平多值」；嵌套对象、可选键仍用 JSONB。

步骤 5：时区行为摸一眼

SHOW timezone;

SELECT
  NOW() AS timestamptz_now,
  NOW() AT TIMESTAMP AS timestamp_naive;

-- 建列时推荐
-- created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

Java 侧 Instant / OffsetDateTime 与 timestamptz 更合拍；MySQL DATETIME + serverTimezone 是另一套故事。

步骤 6：对照笔记

写三行：

1. 主键用 BIGINT 还是 UUID——本练习：cf_track 用 BIGINT，api_key 用 UUID
2. 扩展袋用 JSONB，不留 JSON
3. 时间列默认 timestamptz

讲清楚要点

1. 因为 JSONB 以分解后的二进制存储，所以 可建 GIN、支持 @> 等包含查询；纯 JSON 主要保留输入文本，业务表优先 JSONB。
2. UUID 默认值 用 gen_random_uuid()（或 uuid_generate_v4() 视扩展）；应用层生成也可以，但要统一。
3. 数组是一等公民：ANY / @> / 解嵌套函数都可用；过度嵌套数组会变难查，转 JSONB。
4. timestamptz 存的是绝对时间点：会话时区影响「显示」，不改变那一瞬间。
5. 类型选择服务查询：常按 slug 等值查 → 文本 + B-tree；常按文档包含查 → JSONB + GIN。
6. 与本站 MySQL 对照：生产可用 JSON 列但索引弱；布尔常用 TINYINT(1)。学 PG 时改用原生 BOOLEAN / JSONB，回去写 MySQL 时再切回方言。

你可能会懵的地方

• ::jsonb 是什么？
  类型转换。字符串字面量先标成 jsonb，避免被当成 unknown/text 导致运算符不匹配。

• 数组下标从几开始？
  PostgreSQL 数组默认下标从 1 开始（不是 0）。tags[1] 是第一个元素。

• JSONB 里数字和文本混用？
  ->>'x' 得到文本；要数字再 ::int。比较时类型要一致。

• 没有 pgcrypto 怎么办？
  PG 13+ 核心常有 gen_random_uuid()。先 SELECT gen_random_uuid(); 试；不行再 CREATE EXTENSION。

• TIMESTAMPTZ 是不是「存了时区字符串」？
  内部是 UTC 绝对时间；时区用于输入输出解释。别和「带偏移的字符串列」混为一谈。

错法对照

错法	问题	正解
列类型用 JSON 却要建 GIN 重度查询	能力弱、徒增困惑	用 JSONB
全部主键改 UUID「更现代」	索引更宽、无必要	OLTP 自增仍常见；按场景选
TIMESTAMP 当创建时间且跨区部署	夏令时/时区错乱	TIMESTAMPTZ
用字符串拼「逗号分隔标签」	难约束、难索引	TEXT[] 或 JSONB 数组
MySQL 的 DATETIME 字面量习惯硬套	驱动/ORM 映射怪异	统一 timestamptz + Instant

自测三问

1. 何时用 TEXT[]，何时把多值放进 JSONB？
2. metadata -> 'tags' 与 metadata ->> 'featured' 返回类型有何不同？
3. 为什么本站 cf_track.id 用 BIGINT，而本课 api_key.id 用 UUID，并不矛盾？

小结一句话

UUID、JSONB、数组、timestamptz 是 PostgreSQL 相对 MySQL 最常碰到的类型差异；选对类型，查询与映射才顺。

官方延伸

• PostgreSQL：Data Types
• PostgreSQL：UUID 类型