第2章数据类型与表设计 — PostgreSQL 实战指南

2.1 数值类型

PostgreSQL 提供多种数值类型，选对类型既节省存储又保证精度：

类型	别名	存储大小	范围	适用场景
`smallint`	int2	2 bytes	-32768 ~ 32767	状态码、评分（1-5 星）
`integer`	int, int4	4 bytes	±21 亿	一般计数、普通 ID
`bigint`	int8	8 bytes	±92 千兆	雪花 ID、大表主键
`numeric(p,s)`	decimal	可变	精度 p，小数 s 位	金融金额（不能用 float！）
`real`	float4	4 bytes	6 位十进制精度	科学计算（允许精度损失）
`double precision`	float8	8 bytes	15 位十进制精度	坐标、统计数据
`serial`	—	4 bytes	1 ~ 2147483647	旧式自增 ID（不推荐，见下）
`bigserial`	—	8 bytes	1 ~ 9223372036854775807	旧式大表自增（不推荐）

⚠️

金额绝对不要用 float/real！浮点数存在精度问题：SELECT 0.1 + 0.2 在 float 下得到 0.30000000000000004。金融金额必须使用 numeric(15,2) 或将金额存储为最小单位整数（分/厘）。

SQL-- 正确：用 numeric 存储金额
CREATE TABLE payments (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount    NUMERIC(15, 2) NOT NULL,   -- 最多13位整数+2位小数
  currency  CHAR(3) NOT NULL DEFAULT 'CNY'
);

-- 验证精度
SELECT (0.1::NUMERIC + 0.2::NUMERIC);  -- 结果：0.3（正确）
SELECT (0.1::FLOAT8 + 0.2::FLOAT8);   -- 结果：0.30000000000000004（错误）

2.2 字符类型

类型	说明	最大长度	适用场景
`text`	变长，无长度限制	约 1 GB	PostgreSQL 首选，文章、备注、任意字符串
`varchar(n)`	变长，有上限	n 个字符	需要强制长度约束时（如 email、用户名）
`char(n)`	固定长度，不足补空格	n 个字符	极少使用，如固定格式代码（ISO 国家码 CHAR(2)）

💡

PostgreSQL 中 text vs varchar(n) 性能一致在 PG 内部，text 和 varchar 的存储机制完全相同，无性能差异。使用 text 更简洁；当需要长度约束时才用 varchar(n)，约束通过 CHECK 约束实现。

SQL-- text 是首选
CREATE TABLE users (
  username  TEXT NOT NULL,
  email     TEXT NOT NULL,
  bio       TEXT,
  -- 用 CHECK 约束代替 varchar(n) 的长度限制
  CONSTRAINT username_length CHECK (char_length(username) BETWEEN 2 AND 50),
  CONSTRAINT email_format  CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$')
);

2.3 日期与时间类型

类型	存储	精度	说明
`date`	4 bytes	日	仅日期，如 2024-01-15
`time`	8 bytes	微秒	仅时间，不含日期，不含时区
`timestamp`	8 bytes	微秒	日期+时间，不含时区（存储时丢弃时区信息）
`timestamptz`	8 bytes	微秒	日期+时间+时区（生产环境强烈推荐）
`interval`	16 bytes	微秒	时间段，如 '7 days'、'2 hours 30 minutes'

⚠️

时区陷阱：始终使用 timestamptztimestamp（without time zone）存储时会忽略时区，导致跨时区系统数据混乱。timestamptz 内部统一存储为 UTC，显示时自动转换为服务器时区。多时区应用必须用 timestamptz。

SQL-- timestamptz 实战示例
CREATE TABLE events (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       TEXT NOT NULL,
  starts_at   TIMESTAMPTZ NOT NULL,
  ends_at     TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 插入时带时区
INSERT INTO events (title, starts_at)
VALUES ('会议', '2024-03-15 09:00:00+08');  -- +08 表示北京时间

-- 在不同时区显示同一时刻
SELECT
  starts_at AT TIME ZONE 'Asia/Shanghai'  AS beijing_time,
  starts_at AT TIME ZONE 'UTC'            AS utc_time,
  starts_at AT TIME ZONE 'America/New_York' AS ny_time
FROM events;

-- interval 运算
SELECT
  NOW() + INTERVAL '7 days'   AS next_week,
  NOW() - INTERVAL '1 month'  AS last_month,
  age(NOW(), '2000-01-01'::DATE) AS age_since_2000;

2.4 布尔与 UUID 类型

SQL-- 布尔类型（TRUE / FALSE / NULL）
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

-- 布尔接受多种输入格式
SELECT 'true'::BOOLEAN, 'yes'::BOOLEAN, '1'::BOOLEAN,  -- 全部为 TRUE
       'false'::BOOLEAN, 'no'::BOOLEAN, '0'::BOOLEAN;  -- 全部为 FALSE

-- UUID：全球唯一标识符，128 位，无需中央协调
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";    -- 启用 UUID 扩展

-- 或使用 PG 13+ 内置的 gen_random_uuid()（无需扩展）
CREATE TABLE api_keys (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    BIGINT NOT NULL,
  key_hash   TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 插入时自动生成 UUID
INSERT INTO api_keys (user_id, key_hash) VALUES (1, 'abc123hash')
RETURNING id;
-- 输出: id = 550e8400-e29b-41d4-a716-446655440000

2.5 数组类型

PostgreSQL 原生支持数组，任何数据类型都可以是数组：integer[]、text[]、jsonb[]。

SQL-- 建表时使用数组
CREATE TABLE products (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT NOT NULL,
  tags     TEXT[]  DEFAULT '{}',        -- 标签数组，默认空数组
  scores   INTEGER[]                       -- 整数数组
);

-- 插入数组数据（两种语法）
INSERT INTO products (name, tags, scores) VALUES
  ('MacBook Pro', ARRAY['laptop', 'apple', 'premium'], ARRAY[95, 88, 92]),
  ('Dell XPS',    '{laptop,windows}',                   '{90,85}');

-- 查询数组
SELECT name, tags[1] AS first_tag FROM products;  -- 数组下标从 1 开始！

-- 包含查询：tags 包含 'laptop'
SELECT * FROM products WHERE 'laptop' = ANY(tags);

-- 数组包含操作符 @>（包含）
SELECT * FROM products WHERE tags @> ARRAY['laptop', 'apple'];

-- 展开数组为行（unnest）
SELECT name, unnest(tags) AS tag FROM products;

-- 数组聚合
SELECT array_agg(name ORDER BY name) AS all_products FROM products;

2.6 JSONB 类型

JSONB（JSON Binary）是 PostgreSQL 最强大的类型之一，以二进制格式存储 JSON，支持索引和高效查询。

📋

JSON vs JSONBjson 以文本原样存储，每次查询需重新解析；jsonb 以分解的二进制格式存储，写入稍慢但查询极快，支持 GIN 索引。99% 情况下选 jsonb。

⚡

JSONB 的局限不保留键的原始顺序（会重新排序）；不保留重复键（保留最后一个）；null 语义与 SQL NULL 不同（jsonb null 是值，SQL NULL 是缺失）。

SQL-- JSONB 常用操作符
SELECT
  '{"name":"Alice","age":30,"address":{"city":"Beijing"}}'::JSONB
  ->  'name'        AS get_key_json,      -- 返回 jsonb: "Alice"
  ->> 'name'        AS get_key_text,      -- 返回 text: Alice（无引号）
  ->  'address' -> 'city' AS nested,    -- 嵌套访问
  #>  '{address,city}' AS path_json,     -- 路径访问，返回 jsonb
  #>> '{address,city}' AS path_text;    -- 路径访问，返回 text

-- 建表示例：用户档案（半结构化数据）
CREATE TABLE user_profiles (
  user_id    BIGINT PRIMARY KEY,
  profile    JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO user_profiles VALUES
(1, '{"name":"Alice","skills":["Go","PostgreSQL"],"level":5}'),
(2, '{"name":"Bob","skills":["Python","Redis"],"level":3}'),
(3, '{"name":"Carol","skills":["Java","Kafka","Flink"],"level":8}');

-- 包含查询：profile 中包含 {"level": 5} 的记录
SELECT * FROM user_profiles WHERE profile @> '{"level":5}';

-- 键存在查询：检查是否有 "skills" 键
SELECT * FROM user_profiles WHERE profile ? 'skills';

-- 修改 JSONB 字段（jsonb_set）
UPDATE user_profiles
SET profile = jsonb_set(profile, '{level}', '6')
WHERE user_id = 1;

-- 追加数组元素
UPDATE user_profiles
SET profile = jsonb_set(
  profile,
  '{skills}',
  (profile->'skills') || '"Rust"'::JSONB
)
WHERE user_id = 1;

-- 展开 JSONB 为关系行
SELECT user_id, jsonb_each_text(profile) FROM user_profiles WHERE user_id = 1;

2.7 枚举类型

SQL-- 创建枚举类型
CREATE TYPE order_status AS ENUM (
  'pending', 'confirmed', 'shipped', 'delivered', 'cancelled'
);

CREATE TYPE user_role AS ENUM ('admin', 'editor', 'viewer');

-- 在表中使用枚举
CREATE TABLE orders (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status  order_status NOT NULL DEFAULT 'pending',
  total   NUMERIC(12, 2) NOT NULL
);

-- 枚举支持排序（按定义顺序）
SELECT * FROM orders ORDER BY status;  -- pending < confirmed < shipped < ...

-- 添加新枚举值（PG 9.1+，需注意不能删除值）
ALTER TYPE order_status ADD VALUE 'refunding' AFTER 'delivered';

-- 查看所有枚举值
SELECT * FROM pg_enum WHERE enumtypid = 'order_status'::REGTYPE;

⚠️

枚举类型的局限枚举值一旦添加无法删除，只能添加。如果枚举值经常变化，考虑用 TEXT + CHECK CONSTRAINT 代替，或建立独立的枚举表（lookup table），通过外键约束。

2.8 主键选型与最佳实践

方案一：BIGINT IDENTITY（推荐，大多数场景）

SQL-- PG 10+ 推荐的自增方式（替代旧式 SERIAL）
CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  -- GENERATED ALWAYS：不允许手动插入 ID（更安全）
  -- GENERATED BY DEFAULT：允许手动指定 ID（迁移数据时有用）
  username   TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

方案二：UUID（分布式/微服务场景）

SQL-- UUID v4（随机）：防止 ID 枚举攻击，可跨库合并
CREATE TABLE sessions (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id    BIGINT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL
);
-- 注意：UUID 主键的 B-Tree 索引因随机性会产生页面碎片（page split）
-- 大表可使用 UUIDv7（时间有序）或 pg_ulid 扩展改善写入性能

时区统一规范

SQL-- 全局设置服务器时区为 UTC（推荐）
-- 在 postgresql.conf 中设置：
-- timezone = 'UTC'

-- 或在会话级别设置
SET timezone = 'Asia/Shanghai';

-- 查看当前时区
SHOW timezone;

-- 最佳实践：数据库存 UTC，应用层转换显示时区
-- 所有 created_at / updated_at 字段统一用 TIMESTAMPTZ + DEFAULT NOW()
CREATE TABLE posts (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      TEXT NOT NULL,
  body       TEXT,
  author_id  BIGINT NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

📌

本章小结金额用 numeric(p,s)，不用 float。字符串首选 text。时间戳始终用 timestamptz 存 UTC。主键用 BIGINT GENERATED ALWAYS AS IDENTITY（普通场景）或 UUID DEFAULT gen_random_uuid()（分布式场景）。JSONB 是半结构化数据的首选，->/->>/>/@> 是最常用操作符。

← 上一章PostgreSQL 简介与安装下一章 →SQL 基础与进阶查询