PostgreSQL 数据类型
PostgreSQL 数据类型全解析
PostgreSQL 的数据类型极其丰富，不仅支持标准 SQL 类型，还提供 数组、JSON、范围、几何、UUID、自定义类型 等高级特性。
本文按 分类 + 用法 + 最佳实践 全面讲解。
一、数字类型
| 类型 | 存储大小 | 范围 | 推荐场景 |
|---|---|---|---|
SMALLINT | 2 字节 | -32,768 ~ 32,767 | 小整数（如状态码、评分 1~5） |
INTEGER / INT | 4 字节 | -2.1B ~ 2.1B | 通用整数（主键、计数） |
BIGINT | 8 字节 | -9.2E18 ~ 9.2E18 | 大计数、雪花 ID |
SERIAL | 4 字节 | 1 ~ 2.1B | 自增 ID（int） |
BIGSERIAL | 8 字节 | 1 ~ 9.2E18 | 大表自增 ID |
NUMERIC(p,s) | 可变 | 精确到小数点后 s 位 | 金融、金额（推荐） |
DECIMAL(p,s) | 同上 | 同 NUMERIC | 同上 |
REAL | 4 字节 | 6 位小数精度 | 浮点科学计算 |
DOUBLE PRECISION | 8 字节 | 15 位小数精度 | 科学计算、坐标 |
注意：不要用
FLOAT存金额！用NUMERIC(19,4)或MONEY（但MONEY有时区问题，不推荐）
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
quantity INTEGER NOT NULL CHECK (quantity > 0),
price NUMERIC(10,2) NOT NULL, -- 最大 8 位整数 + 2 位小数
total NUMERIC(12,2) GENERATED ALWAYS AS (quantity * price) STORED
);二、字符类型
| 类型 | 存储 | 说明 | 推荐 |
|---|---|---|---|
CHAR(n) | 固定 n 字节 | 不足补空格 | 极少用 |
VARCHAR(n) | 可变，最多 n | 通用文本 | 推荐 |
TEXT | 可变，无限（1GB） | 无长度限制 | 强烈推荐 |
CITEXT | 可变 | 忽略大小写（需扩展） | 邮箱、用户名 |
-- 推荐写法
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email CITEXT UNIQUE, -- 需 CREATE EXTENSION citext;
bio TEXT
);三、布尔类型
| 类型 | 存储 | 值 |
|---|---|---|
BOOLEAN | 1 字节 | TRUE / FALSE / NULL |
is_active BOOLEAN DEFAULT true,
is_verified BOOLEAN NOT NULL DEFAULT false四、时间日期类型
| 类型 | 存储 | 说明 | 推荐 |
|---|---|---|---|
DATE | 4 字节 | 日期（无时间） | 生日 |
TIME | 8 字节 | 时间（无日期） | 很少单独用 |
TIMETZ | 12 字节 | 带时区时间 | 少用 |
TIMESTAMP | 8 字节 | 日期+时间（无时区） | 旧系统 |
TIMESTAMPTZ | 8 字节 | 带时区时间戳 | 强烈推荐 |
INTERVAL | 16 字节 | 时间间隔 | 超时、统计 |
CREATE TABLE events (
id SERIAL PRIMARY KEY,
name TEXT,
created_at TIMESTAMPTZ DEFAULT now(), -- 推荐
updated_at TIMESTAMPTZ DEFAULT now(),
event_date DATE,
duration INTERVAL DEFAULT '1 hour'
);最佳实践：所有时间戳一律用
TIMESTAMPTZ，自动处理时区。
五、JSON 类型（PostgreSQL 杀手级特性）
| 类型 | 说明 | 推荐 |
|---|---|---|
JSON | 存储原始 JSON 文本 | 仅用于存储 |
JSONB | 二进制格式，支持索引、操作符 | 强烈推荐 |
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
specs JSONB
);
-- 插入
INSERT INTO products (name, specs) VALUES ('Phone', '{
"brand": "Apple",
"price": 999,
"features": ["5G", "OLED"],
"in_stock": true
}');
-- 查询
SELECT name, specs->>'brand' AS brand FROM products;
SELECT * FROM products WHERE specs @> '{"in_stock": true}';
SELECT * FROM products WHERE specs->'features' ? '5G';
-- 索引
CREATE INDEX idx_products_specs ON products USING GIN (specs);六、数组类型（Array）
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT,
tags TEXT[], -- 文本数组
scores INTEGER[] DEFAULT '{}'
);
-- 插入
INSERT INTO articles (title, tags) VALUES ('PG 教程', '{"database","sql","json"}');
-- 查询
SELECT * FROM articles WHERE tags @> ARRAY['sql'];
SELECT title, tags[1] AS first_tag FROM articles;
-- 更新
UPDATE articles SET tags = tags || 'new' WHERE id = 1;
UPDATE articles SET tags = array_remove(tags, 'old') WHERE id = 1;
-- 索引
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);七、UUID 类型
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE sessions (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id INTEGER,
expires_at TIMESTAMPTZ
);优点：分布式唯一、无序插入性能好
缺点：16 字节，比BIGINT大
八、网络地址类型
| 类型 | 说明 |
|---|---|
INET | IPv4/IPv6 地址 |
CIDR | IP 网段 |
MACADDR | MAC 地址 |
ip_address INET NOT NULL,
network CIDR九、范围类型（Range）
CREATE EXTENSION IF NOT EXISTS int4range;
CREATE TABLE reservations (
id SERIAL PRIMARY KEY,
room_id INTEGER,
during TSRANGE NOT NULL, -- 时间范围
EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);
-- 插入
INSERT INTO reservations (room_id, during)
VALUES (101, '[2025-11-03 14:00, 2025-11-03 16:00)');十、货币类型（慎用）
price MONEY不推荐：有本地化问题（如 $ vs ¥），精度不一致。
推荐：用NUMERIC(19,4)+ 应用层格式化。
十一、几何类型（需 PostGIS）
CREATE EXTENSION postgis;
location GEOMETRY(POINT, 4326), -- WGS84 经纬度
path GEOMETRY(LINESTRING, 4326)十二、自定义类型（Composite Type）
CREATE TYPE address AS (
city TEXT,
street TEXT,
zip_code VARCHAR(10)
);
CREATE TABLE companies (
id SERIAL PRIMARY KEY,
name TEXT,
address address
);
-- 插入
INSERT INTO companies (name, address)
VALUES ('xAI', ROW('Beijing', 'Zhongguancun', '100000'));十三、枚举类型（Enum）
CREATE TYPE mood AS ENUM ('happy', 'sad', 'angry');
CREATE TABLE daily_log (
id SERIAL PRIMARY KEY,
feeling mood NOT NULL
);
INSERT INTO daily_log (feeling) VALUES ('happy');
-- ALTER TYPE mood ADD VALUE 'excited'; -- 需在末尾添加十四、序列（Sequence）
CREATE SEQUENCE user_id_seq START 1000 INCREMENT 2;
CREATE TABLE users (
id INTEGER DEFAULT nextval('user_id_seq') PRIMARY KEY
);
SERIAL内部就是序列，推荐使用SERIAL。
十五、类型转换（CAST）
-- 显式
SELECT '123'::INTEGER;
SELECT CAST('2025-11-03' AS DATE);
-- 隐式（谨慎）
SELECT 100 + '50'; -- 自动转成 150十六、类型选择最佳实践（Checklist）
| 场景 | 推荐类型 |
|---|---|
| 主键 | BIGSERIAL 或 UUID |
| 用户名 | VARCHAR(50) 或 CITEXT |
| 邮箱 | CITEXT |
| 描述 | TEXT |
| 金额 | NUMERIC(19,4) |
| 时间 | TIMESTAMPTZ |
| 状态 | BOOLEAN 或 SMALLINT |
| 标签 | TEXT[] + GIN 索引 |
| 配置 | JSONB + GIN 索引 |
| IP 地址 | INET |
十七、查看类型信息（psql 命令）
\d tablename -- 查看表结构
\dt+ -- 查看表大小
SELECT typname, typlen FROM pg_type WHERE typname = 'int4';十八、常见错误
| 错误 | 正确做法 |
|---|---|
VARCHAR 无长度 | 用 TEXT |
用 FLOAT 存钱 | 用 NUMERIC |
时间用 VARCHAR | 用 TIMESTAMPTZ |
| 数组不用索引 | 加 GIN 索引 |
总结：一图记住核心类型
整数 → INTEGER / BIGSERIAL
文本 → TEXT
邮箱 → CITEXT
时间 → TIMESTAMPTZ
金额 → NUMERIC(19,4)
配置 → JSONB
标签 → TEXT[]
唯一ID → UUID
状态 → BOOLEAN现在就动手：
创建一个用户表，包含 id(BIGSERIAL)、username(CITEXT)、profile(JSONB)、tags(TEXT[])、created_at(TIMESTAMPTZ)，插入 3 条数据，然后用 JSON 查询出 profile.level > 5 的用户。
需要我生成：
- 完整建表示例（含所有类型）？回复
建表示例 - JSONB vs 关系表性能对比？回复
性能对比 - 自动生成测试数据的 SQL？回复
测试数据
随时告诉我！