一、数据类型体系概览
PostgreSQL 拥有极其丰富的数据类型系统，既包含标准 SQL 类型，也扩展了许多高级类型。
1.1 数据类型分类总览
| 分类 |
主要类型 |
特点 |
应用场景 |
| 数值类型 |
整数、小数、浮点数 |
精确/近似计算 |
统计、财务、科学计算 |
| 字符串类型 |
CHAR, VARCHAR, TEXT |
文本存储与处理 |
用户信息、文章内容 |
| 布尔类型 |
BOOLEAN |
逻辑判断 |
状态标志、开关设置 |
| 日期时间 |
DATE, TIME, TIMESTAMP |
时间处理 |
日志、事件、计划 |
| 二进制数据 |
BYTEA |
原始字节存储 |
文件、图像、加密数据 |
| 枚举类型 |
ENUM |
预定义值集合 |
状态码、分类标签 |
| 几何类型 |
POINT, LINE, CIRCLE |
空间数据 |
地理位置、图形计算 |
| 网络类型 |
INET, CIDR, MACADDR |
网络地址 |
设备管理、日志分析 |
| JSON 类型 |
JSON, JSONB |
半结构化数据 |
配置、API 数据 |
| 数组类型 |
type[] |
同类型集合 |
标签、多值属性 |
| 范围类型 |
数值/日期范围 |
连续区间 |
时间段、价格区间 |
| 特殊类型 |
UUID, XML, TSVECTOR |
特定用途 |
唯一标识、全文搜索 |
二、数值类型详解
2.1 整数类型对比
| 类型 |
别名 |
存储空间 |
范围 |
描述 |
推荐场景 |
smallint |
int2 |
2 字节 |
-32,768 到 32,767 |
小范围整数 |
年龄、数量较小计数 |
integer |
int4 |
4 字节 |
-2.1亿 到 2.1亿 |
标准整数 |
ID、计数、大多数数值 |
bigint |
int8 |
8 字节 |
±9.2×10¹⁸ |
大范围整数 |
大数据量、金融金额 |
2.2 自增序列类型
| 类型 |
基础类型 |
存储空间 |
范围 |
描述 |
smallserial |
smallint |
2 字节 |
1 到 32,767 |
自增小整数 |
serial |
integer |
4 字节 |
1 到 2.1亿 |
自增整数（推荐） |
bigserial |
bigint |
8 字节 |
1 到 9.2×10¹⁸ |
自增大整数 |
示例：
CREATE TABLE users (id SERIAL PRIMARY KEY, -- 自增主键age SMALLINT, -- 年龄login_count INTEGER, -- 登录次数big_number BIGINT -- 大数值
);
2.3 精确小数类型
| 类型 |
别名 |
存储空间 |
精度控制 |
描述 |
适用场景 |
decimal(p,s) |
numeric(p,s) |
可变 |
精确 |
定点小数 |
财务计算、精确测量 |
numeric(p,s) |
decimal(p,s) |
可变 |
精确 |
定点小数 |
同 decimal |
精度参数说明：
CREATE TABLE financial_data (price DECIMAL(10,2), -- 价格：总共10位，2位小数tax_rate NUMERIC(5,4), -- 税率：0.0000 到 0.9999amount NUMERIC -- 任意精度
);
2.4 浮点数类型
| 类型 |
别名 |
存储空间 |
精度 |
范围 |
特点 |
real |
float4 |
4 字节 |
6位 |
±1.18×10⁻³⁸ 到 ±3.4×10³⁸ |
单精度，性能好 |
double precision |
float8 |
8 字节 |
15位 |
±2.23×10⁻³⁰⁸ 到 ±1.79×10³⁰⁸ |
双精度，更精确 |
示例：
CREATE TABLE scientific_data (temperature REAL, -- 温度测量precise_calculation DOUBLE PRECISION -- 精确计算
);
三、字符串与文本类型
3.1 字符串类型对比
| 类型 |
描述 |
存储空间 |
特点 |
推荐场景 |
char(n) |
定长字符串 |
n 字节 |
不足补空格，检索快 |
固定长度代码（如国家代码） |
varchar(n) |
变长字符串 |
实际长度+1 |
长度限制，节省空间 |
有限长度文本（用户名、邮箱） |
text |
变长文本 |
实际长度+1 |
无长度限制，功能强 |
任意长度文本（内容、描述） |
3.2 字符串类型选择指南
| 场景 |
推荐类型 |
理由 |
| 固定长度编码 |
CHAR(n) |
存储效率高，性能好 |
| 有限可变文本 |
VARCHAR(n) |
空间优化，有约束 |
| 任意长度内容 |
TEXT |
无限制，最灵活 |
| 不确定长度 |
TEXT |
避免长度限制问题 |
示例：
CREATE TABLE user_data (country_code CHAR(2), -- 国家代码，固定2位username VARCHAR(50), -- 用户名，最多50字符email VARCHAR(100), -- 邮箱地址biography TEXT, -- 个人简介，无长度限制phone CHAR(11) -- 电话号码
);
四、日期时间类型
4.1 日期时间类型详解
| 类型 |
完整名称 |
存储空间 |
范围 |
精度 |
示例 |
date |
DATE |
4 字节 |
4713 BC - 5874897 AD |
1 天 |
'2024-01-15' |
time |
TIME |
8 字节 |
00:00:00 - 24:00:00 |
1 微秒 |
'14:30:25.123456' |
timestamp |
TIMESTAMP |
8 字节 |
4713 BC - 5874897 AD |
1 微秒 |
'2024-01-15 14:30:25' |
timestamptz |
TIMESTAMP WITH TIME ZONE |
8 字节 |
4713 BC - 5874897 AD |
1 微秒 |
'2024-01-15 14:30:25+08' |
interval |
INTERVAL |
16 字节 |
±178000000 年 |
1 微秒 |
'2 days 3 hours' |
4.2 日期时间操作示例
-- 创建表
CREATE TABLE events (event_date DATE, -- 事件日期start_time TIME, -- 开始时间created_at TIMESTAMP, -- 创建时间（无时区）updated_at TIMESTAMPTZ DEFAULT NOW(), -- 更新时间（带时区）duration INTERVAL -- 持续时间
);-- 常用函数
SELECT NOW() AS current_time, -- 当前时间戳CURRENT_DATE AS today, -- 当前日期CURRENT_TIME AS now_time, -- 当前时间EXTRACT(YEAR FROM created_at) AS year, -- 提取年份AGE(created_at) AS time_ago; -- 计算时间间隔
五、布尔与二进制类型
5.1 布尔类型
| 类型 |
存储空间 |
取值 |
有效输入 |
boolean |
1 字节 |
TRUE, FALSE, NULL |
true/false, 't'/'f', 'yes'/'no', '1'/'0' |
示例：
CREATE TABLE settings (is_active BOOLEAN DEFAULT TRUE,is_verified BOOLEAN DEFAULT FALSE,newsletter_optin BOOLEAN
);-- 多种写入方式
INSERT INTO settings VALUES (TRUE, FALSE, NULL),('t', 'f', 'yes'),('1', '0', 'no');
5.2 二进制类型
| 类型 |
描述 |
存储空间 |
格式 |
应用场景 |
bytea |
二进制数据 |
1-4字节+实际大小 |
十六进制或转义格式 |
文件存储、图片、加密数据 |
示例：
CREATE TABLE documents (id SERIAL PRIMARY KEY,file_name TEXT,file_data BYTEA, -- 存储二进制文件file_size INTEGER
);-- 插入二进制数据
INSERT INTO documents (file_name, file_data)
VALUES ('image.png', E'\\x89504E470D0A1A0A0000000D49484452');
六、高级数据类型
6.1 JSON 类型对比
| 类型 |
存储格式 |
验证 |
索引支持 |
性能 |
推荐度 |
JSON |
文本格式 |
写入时验证 |
有限 |
读取慢，写入快 |
★★☆☆☆ |
JSONB |
二进制格式 |
写入时验证 |
完整 |
读取快，写入稍慢 |
★★★★★ |
JSONB 示例：
CREATE TABLE user_profiles (id SERIAL PRIMARY KEY,profile_data JSONB, -- 推荐使用 JSONBcreated_at TIMESTAMPTZ DEFAULT NOW()
);-- 插入 JSON 数据
INSERT INTO user_profiles (profile_data) VALUES
('{"name": "张三","age": 30,"address": {"city": "北京","street": "朝阳区"},"hobbies": ["读书", "游泳", "编程"]
}');-- JSON 查询操作
SELECT profile_data->>'name' AS name, -- 获取字符串值profile_data->'address'->>'city' AS city, -- 嵌套查询profile_data->'hobbies'->>0 AS primary_hobby -- 数组元素
FROM user_profiles;-- JSON 路径查询
SELECT profile_data#>>'{address,city}' AS city FROM user_profiles;
6.2 数组类型
| 声明方式 |
示例 |
描述 |
数据类型[] |
INTEGER[] |
整数数组 |
数据类型[长度] |
VARCHAR(50)[] |
字符串数组 |
| 多维数组 |
TEXT[][] |
文本二维数组 |
数组操作示例：
CREATE TABLE products (id SERIAL PRIMARY KEY,name TEXT,tags TEXT[], -- 标签数组prices DECIMAL[], -- 价格历史dimensions INTEGER[3] -- 固定长度数组
);-- 插入数组数据
INSERT INTO products (name, tags, prices, dimensions) VALUES
('笔记本电脑', '{"电子产品","电脑","便携"}', '{4999.00, 4599.00, 4799.00}', '{30,20,5}');-- 数组查询
SELECT name,tags[1] AS primary_tag, -- 访问数组元素array_length(tags, 1) AS tag_count, -- 数组长度unnest(tags) AS individual_tag -- 展开数组
FROM products
WHERE '电子产品' = ANY(tags); -- 数组包含检查
6.3 范围类型
| 类型 |
描述 |
示例 |
适用场景 |
int4range |
整数范围 |
[1,10) |
年龄范围、数量区间 |
int8range |
大整数范围 |
[100,1000] |
ID 范围、大数值区间 |
numrange |
数值范围 |
(10.5,20.5] |
价格区间、测量范围 |
tsrange |
时间戳范围 |
[2024-01-01,2024-12-31] |
时间段、有效期 |
daterange |
日期范围 |
[2024-01-01,2024-06-30] |
日期区间 |
范围类型示例：
CREATE TABLE reservations (id SERIAL PRIMARY KEY,room_id INTEGER,period TSRANGE, -- 时间段price_range NUMRANGE -- 价格范围
);INSERT INTO reservations (room_id, period, price_range) VALUES
(1, '[2024-01-15 14:00, 2024-01-20 10:00)', '[100.00, 200.00)');-- 范围查询
SELECT * FROM reservations
WHERE period @> '2024-01-16 12:00'::TIMESTAMP; -- 包含时间点
七、特殊数据类型
7.1 网络地址类型
| 类型 |
描述 |
存储空间 |
示例 |
适用场景 |
inet |
IP地址（IPv4/IPv6） |
7或19字节 |
'192.168.1.1' |
设备IP管理 |
cidr |
网络地址块 |
7或19字节 |
'192.168.1.0/24' |
子网划分 |
macaddr |
MAC地址 |
6字节 |
'08:00:2b:01:02:03' |
网络设备 |
示例：
CREATE TABLE network_devices (id SERIAL PRIMARY KEY,device_name TEXT,ip_address INET,network CIDR,mac_address MACADDR
);-- 网络操作
SELECT ip_address,HOST(ip_address) AS host, -- 提取主机部分MASKLEN(network) AS mask_length -- 子网掩码长度
FROM network_devices
WHERE ip_address << network; -- IP是否在网段内
7.2 其他特殊类型
| 类型 |
描述 |
存储空间 |
示例 |
用途 |
uuid |
通用唯一标识符 |
16字节 |
'550e8400-e29b-41d4-a716-446655440000' |
分布式ID |
xml |
XML数据 |
可变 |
<book><title>SQL</title></book> |
结构化文档 |
money |
货币金额 |
8字节 |
'$123.45' |
金融数据 |
tsvector |
全文搜索向量 |
可变 |
'cat:1 dog:2' |
全文索引 |
tsquery |
全文搜索查询 |
可变 |
'cat & dog' |
搜索查询 |
UUID 示例：
-- 启用 UUID 扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";CREATE TABLE distributed_users (user_id UUID DEFAULT uuid_generate_v4(), -- 自动生成 UUIDusername TEXT,created_at TIMESTAMPTZ DEFAULT NOW()
);INSERT INTO distributed_users (username) VALUES ('john_doe');
八、枚举与几何类型
8.1 枚举类型
-- 创建枚举类型
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');
CREATE TYPE product_size AS ENUM ('small', 'medium', 'large', 'x-large');-- 使用枚举
CREATE TABLE users (id SERIAL PRIMARY KEY,name TEXT,status user_status DEFAULT 'active'
);CREATE TABLE products (id SERIAL PRIMARY KEY,name TEXT,size product_size
);
8.2 几何类型
| 类型 |
描述 |
存储空间 |
示例 |
用途 |
point |
点坐标 |
16字节 |
(3,5) |
地理位置 |
line |
无限直线 |
32字节 |
{1,-1,0} |
几何计算 |
lseg |
线段 |
32字节 |
[(1,2),(3,4)] |
线段表示 |
box |
矩形 |
32字节 |
(1,1),(0,0) |
边界框 |
path |
路径 |
16+16n字节 |
[(0,0),(1,1),(2,0)] |
运动轨迹 |
polygon |
多边形 |
40+16n字节 |
((0,0),(1,0),(1,1),(0,1)) |
区域划分 |
circle |
圆形 |
24字节 |
<(1,1),2> |
圆形区域 |
九、类型选择最佳实践
9.1 数据类型选择指南
| 数据特征 |
推荐类型 |
理由 |
示例 |
| 主键标识 |
SERIAL, BIGSERIAL, UUID |
自动生成，保证唯一性 |
用户ID、订单ID |
| 金额价格 |
NUMERIC/DECIMAL |
精确计算，避免误差 |
商品价格、账户余额 |
| 一般文本 |
TEXT |
无长度限制，灵活 |
文章内容、描述 |
| 固定代码 |
CHAR(n) |
存储效率高 |
国家代码、状态码 |
| 时间戳 |
TIMESTAMPTZ |
时区支持，标准化 |
创建时间、更新时间 |
| 开关状态 |
BOOLEAN |
语义清晰，存储高效 |
是否激活、是否验证 |
| 配置数据 |
JSONB |
灵活结构，查询高效 |
用户配置、元数据 |
| 多个值 |
数组类型 |
避免关联表 |
标签、分类 |
| 唯一标识 |
UUID |
分布式唯一性 |
分布式系统ID |
| 二进制文件 |
BYTEA |
原生二进制支持 |
图片、文档 |
9.2 性能优化建议
| 场景 |
优化建议 |
理由 |
| 频繁查询的数值 |
使用合适范围的整数类型 |
减少存储，提高计算速度 |
| 文本搜索 |
对 TEXT 列创建索引 |
加速文本查询 |
| JSON 查询 |
使用 JSONB + GIN 索引 |
支持各种 JSON 查询 |
| 范围查询 |
使用范围类型 + GiST 索引 |
高效的范围操作 |
| 数组操作 |
对数组列创建 GIN 索引 |
加速数组包含查询 |
十、类型转换与函数
10.1 类型转换方法
-- 显式类型转换
SELECT '123'::INTEGER;
SELECT CAST('123' AS INTEGER);
SELECT '2024-01-15'::DATE;-- 隐式类型转换
SELECT 3 + '5'; -- 自动转为数字
SELECT 'Hello' || 123; -- 自动转为文本-- 类型检测
SELECT pg_typeof(123.4); -- 返回数据类型
10.2 常用类型函数
| 类别 |
函数 |
描述 |
示例 |
| 数值函数 |
ROUND(), CEIL(), FLOOR() |
四舍五入、向上/向下取整 |
ROUND(3.14159, 2) → 3.14 |
| 字符串函数 |
LENGTH(), UPPER(), LOWER() |
长度、大小写转换 |
LENGTH('hello') → 5 |
| 日期函数 |
EXTRACT(), DATE_PART(), AGE() |
提取部分、计算间隔 |
EXTRACT(YEAR FROM NOW()) |
| JSON函数 |
->, ->>, jsonb_extract_path() |
JSON 数据提取 |
data->>'name' |
| 数组函数 |
array_length(), unnest() |
数组操作 |
unnest(tags) |
