PostgreSQL 数据类型与表设计

PostgreSQL 系统讲解第二篇：类型系统是 PG 最大的优势之一——本篇过一遍"值得用的类型"和表设计时的关键选择。

值得优先掌握的类型

类型	用途	备忘
`numeric(p,s)`	精确小数	金额一律用它；`float`/`double` 有浮点误差
`bigint` + IDENTITY	主键	见下节自增方案
`timestamptz`	带时区时间戳	存 UTC 内部表示，显示按会话时区——优先用它而不是 timestamp
`uuid`	原生 UUID 类型	分布式 id；默认无内置生成函数，用 `gen_random_uuid()`（pgcrypto/13+ 内置）
`text`	不限长字符串	PG 里 varchar(n) 与 text 性能相同，长度约束用 CHECK 就行——"都建 text 需要时再限"是常见流派
`text[]`	数组	标签、多值字段一行搞定
`jsonb`	二进制 JSON	可索引、可查询；`json` 只存原文（慢），默认用 jsonb
`bytea`	二进制大对象	大文件仍建议对象存储 + URL

JSONB：PG 的招牌

CREATE TABLE events (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data   jsonb NOT NULL
);

INSERT INTO events (data) VALUES ('{"type": "click", "page": "/home", "meta": {"src": "app"}}');

-- 查询操作符
SELECT data->>'type' FROM events;              -- ->> 取文本；-> 取 jsonb
SELECT data->'meta'->>'src' FROM events;
SELECT * FROM events WHERE data @> '{"type": "click"}';   -- @> 包含，可走 GIN 索引
SELECT * FROM events WHERE data ? 'page';                 -- 是否存在顶层 key

jsonb vs json：jsonb 解析后存储（支持索引、去重键、次序不定），json 存原文（只能整体读）。几乎总是选 jsonb
GIN 索引让 jsonb 查询起飞：CREATE INDEX ON events USING GIN (data);
定位：schema 频繁变化的属性数据用 jsonb；核心业务字段老老实实建列（可约束、可统计、类型安全）

数组类型

SELECT * FROM users WHERE 'python' = ANY(tags);   -- 包含元素
SELECT * FROM users WHERE tags @> ARRAY['python', 'go'];   -- 同时包含
UPDATE users SET tags = tags || '{rust}' WHERE id = 1;    -- 追加元素
CREATE INDEX ON users USING GIN (tags);                   -- 数组查询走 GIN

我的记忆钩子：数组解决"多值属性"，jsonb 解决"半结构化"——两者一起上 GIN 索引，是 PG 对 MySQL 最大的一波"原生优势"。

自增三方案：SERIAL vs IDENTITY vs UUID

id serial PRIMARY KEY                                -- 老写法：int + 默认序列
id integer GENERATED BY DEFAULT AS IDENTITY          -- SQL 标准，推荐
id uuid DEFAULT gen_random_uuid()                    -- 分布式场景

IDENTITY 优于 SERIAL：权限更严格（防止手滑覆盖序列）、可 GENERATED ALWAYS 锁死手工插值
序列对象独立存在（\ds 查看），currval/nextval 可手动取号
UUID vs 自增：分布式/无冲突插入选 UUID；单库自增更省空间且索引更友好——别无脑 UUID

DDL 事务性：改表结构不用怕

BEGIN;
ALTER TABLE users ADD COLUMN nickname varchar(64);
-- 看到列名不合适？直接回滚，不用写一堆恢复脚本
ROLLBACK;

MySQL 转过来的第一惊喜：DDL 也是事务的一部分。配合先在测试库演练的习惯，改表结构的心理负担小很多。

⚠️ 但注意：加列是快的，改列类型、加无默认值的唯一约束等操作会锁表重写——大表 DDL 前先查该版本的锁行为（这属于"动手核对"点）。

schema：一个库里的命名空间

CREATE SCHEMA pay;                        -- 业务分区内聚：public(通用) / shop / log
CREATE TABLE shop.orders (...);
SELECT * FROM shop.orders;                -- 跨 schema 用 点 访问
SET search_path TO shop, public;          -- 搜索路径决定不带前缀时先找哪个

用途：一个库服务多个模块/多租户隔离（每租户一个 schema）、扩展对象隔离（pgvector 的对象在 extension 的 schema 里）。

⬅️ 01-PostgreSQL 基础与 MySQL 对照 🏠 00-数据库 ➡️ 03-PostgreSQL 查询进阶

02-PostgreSQL 数据类型与表设计