数据类型 – 我的技术博客

数据类型

这篇讲 PostgreSQL 的核心数据类型——特别是 PG 特有的 SERIAL、JSONB、数组和 UUID。PG 的数据类型比 MySQL 丰富得多，选择正确的类型能显著提升性能和查询表达能力。

数值类型

类型	大小	范围	说明
`SMALLINT`	2 字节	-32768 到 32767	小整数
`INTEGER`	4 字节	-21 亿到 21 亿	常用整型
`BIGINT`	8 字节	很大	大整数
`SERIAL`	4 字节	1 到 21 亿	自增整型（PG 特有语法）
`BIGSERIAL`	8 字节	很大	自增大整型
`DECIMAL(p,s)`	可变	精确小数	金额等精确计算
`REAL`	4 字节	6 位精度	单精度浮点
`DOUBLE PRECISION`	8 字节	15 位精度	双精度浮点

-- SERIAL 本质是 INTEGER + 自动创建序列
CREATE TABLE products (
    id SERIAL PRIMARY KEY,       -- 等同于 INTEGER DEFAULT nextval('products_id_seq')
    price DECIMAL(10, 2),        -- 金额：10 位总长，2 位小数
    stock INTEGER DEFAULT 0
);

字符类型

类型	说明
`VARCHAR(n)`	变长字符串，有长度上限
`CHAR(n)`	定长字符串，不足用空格填充
`TEXT`	变长，无上限——PG 中最常用的字符串类型

PG 中 VARCHAR(n)、CHAR(n) 和 TEXT 的底层存储完全相同，性能没有差异。大多数场景直接用 TEXT 即可，需要限制长度时才用 VARCHAR(n)。这点和 MySQL 不同——MySQL 的 TEXT 有性能损失。

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),   -- 标题有长度限制
    content TEXT,          -- 正文无长度限制
    tags VARCHAR(50)[]     -- 数组！
);

JSON 与 JSONB

这是 PG 最强大的特性之一：

JSON：存储原始 JSON 文本，保留空格和键顺序，写入快但查询慢
JSONB：二进制格式存储，支持索引，查询快但写入稍慢——推荐生产环境用 JSONB

-- 创建含 JSONB 列的表
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    payload JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入 JSON 数据
INSERT INTO events (event_type, payload) VALUES
    ('user_login', '{"user": "alice", "ip": "1.2.3.4", "device": "mobile"}'),
    ('purchase', '{"user": "bob", "amount": 99.9, "items": ["book", "pen"]}');

-- JSONB 查询（运算符）
SELECT * FROM events WHERE payload @> '{"user": "alice"}';           -- 包含
SELECT payload -> 'user' AS username FROM events;                     -- 提取字段（JSONB 类型）
SELECT payload ->> 'user' AS username FROM events;                    -- 提取字段（TEXT 类型）
SELECT payload ? 'amount' AS has_amount FROM events;                  -- 是否存在键
SELECT * FROM events WHERE payload ->> 'user' = 'bob';               -- 条件过滤

-- JSONB 索引（GIN 索引）
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- JSONB 更新
UPDATE events
SET payload = jsonb_set(payload, '{amount}', '129.9')
WHERE id = 2;

JSONB 让 PG 同时拥有关系型和文档型数据库的能力。不需要为了存 JSON 另起一个 MongoDB——PG 的 JSONB 查询性能和 GIN 索引甚至比 MongoDB 更快。

数组类型

PG 原生支持数组——基本类型的数组：

-- 创建含数组的表
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    scores INTEGER[],          -- 整数数组
    hobbies TEXT[]              -- 文本数组
);

-- 插入数组
INSERT INTO students (name, scores, hobbies)
VALUES ('小明', ARRAY[85, 92, 78], ARRAY['篮球', '编程']);

INSERT INTO students (name, scores, hobbies)
VALUES ('小红', '{90, 88, 95}', '{画画, 音乐}');   -- 字符串字面量语法

-- 查询数组
SELECT name, scores[1] AS first_score FROM students;              -- 访问第 1 个元素
SELECT name, scores FROM students WHERE 100 = ANY(scores);       -- 包含某个值
SELECT name, hobbies FROM students WHERE '编程' = ANY(hobbies);
SELECT name, array_length(scores, 1) FROM students;               -- 数组长度
SELECT name, unnest(scores) AS score FROM students;               -- 展开数组

UUID 类型

用 UUID 替代自增 ID 做主键，在分布式系统中更常见：

-- 启用 pgcrypto 扩展
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- 自动生成 UUID
    user_id INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO api_keys (user_id) VALUES (1);
-- id 列自动填充，如：a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

日期/时间类型

类型	说明	示例
`DATE`	日期	`'2024-01-15'`
`TIME`	时间	`'14:30:00'`
`TIMESTAMP`	日期+时间	`'2024-01-15 14:30:00'`
`TIMESTAMPTZ`	带时区的时间戳	`'2024-01-15 14:30:00+08'`
`INTERVAL`	时间间隔	`'1 day'`, `'2 hours'`

SELECT NOW();                          -- 当前时间
SELECT NOW() + INTERVAL '7 days';      -- 7 天后
SELECT AGE(NOW(), '2024-01-01');       -- 计算年龄/时间差

生产环境始终用 TIMESTAMPTZ 而非 TIMESTAMP。TIMESTAMPTZ 会自动转换为 UTC 存储、按客户端时区显示，避免了时区导致的 bug。

一句话小结

PG 的数据类型是其最大的优势之一：TEXT 无性能损失、JSONB 自带文档数据库能力、原生数组省去中间表、UUID 适应分布式场景。选对类型 = 少写 30% 的应用层代码。下一篇讲 SQL 进阶。

最后更新于 2026-06-16

基本操作 SQL 进阶