这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 PostgreSQL 的类型系统丰富且严格。合理选型能提升正确性、存储效率和索引效果。
5.1 数值类型
| 类型 | 适合 |
|---|---|
| smallint / int / bigint | 整数主键、数量 |
| numeric(p,s) | 金额、精度计算 |
| real / double precision | 科学计算、近似值 |
| serial / identity | 自增标识，新项目优先 identity |
金额：
CREATE TABLE payments (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount NUMERIC(12,2) NOT NULL CHECK (amount >= 0),
rate NUMERIC(8,6) NOT NULL
);
不要使用浮点保存资金余额。
5.2 文本类型
| 类型 | 说明 |
|---|---|
| varchar(n) | 变长，可加长度约束 |
| text | 变长，无内置长度限制 |
| char(n) | 定长，自动填充 |
业务上通常使用 text，需要长度规则时用 CHECK 约束表达：
CREATE TABLE members (
email text PRIMARY KEY CHECK (email ~* '^[^@]+@[^@]+$'),
name text NOT NULL CHECK (char_length(name) BETWEEN 1 AND 50)
);
5.3 时间类型
| 类型 | 说明 |
|---|---|
| timestamp | 不带时区 |
| timestamptz | 带时区语义 |
| date | 日期 |
| time / timetz | 时间 |
| interval | 时间间隔 |
建议业务时间使用 timestamptz：
CREATE TABLE audit_log (
event_time TIMESTAMPTZ NOT NULL DEFAULT now(),
payload JSONB NOT NULL
);
时区转换：
SELECT event_time AT TIME ZONE 'Asia/Shanghai'
FROM audit_log;
5.4 布尔与枚举
布尔：
SELECT true, false, null::boolean;
枚举：
CREATE TYPE order_status AS ENUM (
'CREATED', 'PAID', 'SHIPPED', 'CANCELLED'
);
ALTER TYPE order_status ADD VALUE 'REFUNDED';
枚举类型有序、存储紧凑，但删除值和顺序调整受限。需要频繁演进时可使用 text + CHECK 或字典表。
5.5 UUID
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE tenants (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name text NOT NULL
);
UUID 适合分布式生成 ID，但随机 UUID 会降低 B-Tree 写入局部性。可评估有序 UUID 方案或数据库序列。
5.6 数组
CREATE TABLE articles (
id bigint PRIMARY KEY,
tags text[] NOT NULL DEFAULT '{}'
);
查询：
SELECT * FROM articles WHERE 'java' = ANY(tags);
SELECT * FROM articles WHERE tags @> ARRAY['java','sql'];
数组适合一对多且无需独立属性的小列表。若标签需要描述、状态和权限，应建表。
5.7 JSON 与 JSONB
json 保存原始文本，jsonb 是解析后的二进制格式，支持索引和更快操作。
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload JSONB NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
查询：
SELECT
payload ->> 'city' AS city,
payload -> 'props' ->> 'os' AS os
FROM events
WHERE payload @> '{"event":"pay"}';
更新：
UPDATE events
SET payload = payload || '{"amount":99}'::jsonb
WHERE id = 1;
JSONB 适合 schema 演进快的半结构化数据，不适合所有字段都无边界 JSON 化。高频查询字段应生成列并索引。
5.8 生成列
CREATE TABLE events_v2 (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload jsonb NOT NULL,
city text GENERATED ALWAYS AS (payload ->> 'city') STORED,
amount numeric(12,2) GENERATED ALWAYS AS (
(payload ->> 'amount')::numeric
) STORED
);
对生成列建索引：
CREATE INDEX idx_events_city ON events_v2(city);
5.9 类型转换
SELECT '2026-08-25'::date;
SELECT '123'::int;
SELECT now()::date;
SELECT CAST('99.50' AS numeric(10,2));
转换失败会报错。ETL 中可自定义安全转换函数并记录坏数据，而不是在 SQL 中吞掉异常。
5.10 类型设计规范
- 主键明确且稳定；
- 金额使用 numeric；
- 时间使用 timestamptz；
- 状态使用 enum、字典表或受控 text；
- 高频 JSON 字段提升生成列；
- 外键不要只存在于应用代码；
- 避免一个超大宽表包含所有业务域。
本章小结
类型是业务约束的第一层。数值精度、时间时区、文本长度、JSON 结构和生成列都会影响正确性和性能。PostgreSQL 的扩展类型很强，但不要为了灵活牺牲数据治理。
思考题
- numeric 和 double precision 如何选择？
- timestamp 和 timestamptz 有什么区别？
- enum 类型的演进限制是什么？
- JSONB 高频字段如何优化？
- 数组字段什么时候应该拆成表？