好的,PostgreSQL 以其丰富且强大的数据类型系统而闻名,远远超出了标准 SQL 类型。它支持各种基础类型、结构化类型、几何网络类型,甚至允许创建自定义类型。
以下是 PostgreSQL 中主要数据类型的详细分类和说明:
用于存储数字,分为三类:
类型名称 | 存储大小 | 描述 | 范围 |
---|---|---|---|
整数类型 | |||
SMALLINT |
2 字节 | 小范围整数 | -32,768 到 +32,767 |
INTEGER (INT ) |
4 字节 | 常用的整数 | -2,147,483,648 到 +2,147,483,647 |
BIGINT |
8 字节 | 大范围整数 | -9,223,372,036,854,775,808 到 +9,223,372,036,854,775,807 |
自增整数 | |||
SMALLSERIAL |
2 字节 | 自增的小范围整数 | 1 到 32,767 |
SERIAL |
4 字节 | 常用的自增整数 | 1 到 2,147,483,647 |
BIGSERIAL |
8 字节 | 自增的大范围整数 | 1 到 9,223,372,036,854,775,807 |
任意精度类型 | |||
DECIMAL (NUMERIC ) |
可变 | 用户指定精度,**精确计算** | 最多 131,072 位数字;最多 16,383 位小数 |
浮点类型 | |||
REAL |
4 字节 | 可变精度,**不精确** | 6 位十进制数字精度 |
DOUBLE PRECISION |
8 字节 | 可变精度,**不精确** | 15 位十进制数字精度 |
选择建议:
* 主键、数量、年龄等用 SERIAL
或 INTEGER
。
* 金融、货币等要求精确计算的用 DECIMAL/NUMERIC
。
* 科学计算、测量等对精度要求不高的用 DOUBLE PRECISION
。
用于存储文本。
类型名称 | 描述 | 特点 |
---|---|---|
CHARACTER(n) (CHAR(n) ) |
定长字符串 | 不足长度用空格填充,**浪费空间**,适用于长度固定的代码(如国家代码、MD5哈希)。 |
CHARACTER VARYING(n) (VARCHAR(n) ) |
变长字符串 | 最常用。只存储实际字符串,有长度限制。 |
TEXT |
变长字符串 | 更常用。无限长度(实际受限于最大字段大小 1GB)。性能与 VARCHAR 无显著差异,通常首选 TEXT 。 |
选择建议:**无特殊要求,直接用 TEXT
**。需要强制业务长度限制时用 VARCHAR(n)
。
用于存储时间点或时间间隔。
类型名称 | 存储大小 | 描述 | 示例 |
---|---|---|---|
TIMESTAMP [(p )] |
8 字节 | 日期和时间,有时区感知 | 2024-01-01 12:00:00+08 |
TIMESTAMPTZ |
8 字节 | 日期和时间,带时区 | 2024-01-01 12:00:00+08 (存储为UTC) |
DATE |
4 字节 | 只包含日期(无时间) | 2024-01-01 |
TIME [(p )] |
8 字节 | 只包含时间(无日期) | 12:00:00 |
TIMETZ |
12 字节 | 包含时间和时区 | 12:00:00+08 |
INTERVAL |
16 字节 | 时间间隔 | 1 day 02:00:00 |
选择建议:
* 绝大多数情况用 TIMESTAMPTZ
,因为它能正确处理跨时区问题。
* 只需要日期时用 DATE
。
类型名称 | 存储大小 | 描述 |
---|---|---|
BOOLEAN (BOOL ) |
1 字节 | 逻辑布尔值 |
可输入值:TRUE , FALSE , NULL 。也支持简化写法:t , f , yes , no , on , off , 1 , 0 。 |
专门用于高效存储网络地址。
类型名称 | 存储大小 | 描述 | 优势 |
---|---|---|---|
CIDR |
7 或 19 字节 | IPv4 或 IPv6 网络地址 | 有输入检查和专门的操作符(如 >> 包含) |
INET |
7 或 19 字节 | IPv4 或 IPv6 主机地址 | 可以存储网络地址和其子网掩码 |
MACADDR |
6 字节 | MAC 地址 | 有输入检查和专门的操作符 |
选择建议:存储 IP 地址时,**强烈推荐使用 INET
或 CIDR
而不是 TEXT
**,因为它们有输入验证、更高效的存储和丰富的操作函数。
用于存储二进制串(字节序列)。
类型名称 | 描述 | 优势 |
---|---|---|
BYTEA |
变长二进制字符串 | PostgreSQL 的原生二进制类型 |
BLOB / Large Object |
另一种存储大对象的方式 | 通过一个 OID(对象标识符)在系统表 pg_largeobject 中存储,最大 4TB |
选择建议:存储图片、文件、压缩数据等,小于 1GB 用 BYTEA
,极大文件用 Large Object
。
由一组静态、有序的值组成的数据类型,类似其他编程语言中的枚举。
-- 1. 先创建枚举类型
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
-- 2. 在表中使用它
CREATE TABLE person (
name text,
current_mood mood
);
-- 3. 插入数据(只能插入定义好的值)
INSERT INTO person VALUES ('Moe', 'happy'); -- 正确
INSERT INTO person VALUES ('Larry', 'angry'); -- 错误!
用于存储平面上的几何图形。
类型名称 | 存储大小 | 描述 | 表示方法 |
---|---|---|---|
POINT |
16 字节 | 平面上的点 | (x, y) |
LINE |
32 字节 | 无限长的线 | {A, B, C} |
LSEG |
32 字节 | 有限线段 | ((x1,y1), (x2,y2)) |
BOX |
32 字节 | 矩形框 | ((x1,y1), (x2,y2)) |
PATH |
16+16n 字节 | 闭合或开放路径 | ((x1,y1),...) |
POLYGON |
40+16n 字节 | 多边形 | ((x1,y1),...) |
CIRCLE |
24 字节 | 圆 | <(x, y), r> |
PostgreSQL 提供了强大的原生 JSON 支持。
类型名称 | 描述 | 特点 |
---|---|---|
JSON |
存储 JSON 数据 | 存储原始输入(保留空白、键顺序),解析速度稍慢 |
JSONB |
二进制格式的 JSON | 分解后存储,处理速度更快,支持索引,推荐使用 |
选择建议:**几乎总是使用 JSONB
**,除非你需要保留原始格式(如键的顺序)。
CREATE TABLE orders (
id serial PRIMARY KEY,
info JSONB
);
INSERT INTO orders (info) VALUES ('{"customer": "John Doe", "items": {"product": "Beer","qty": 6}}');
-- 使用 -> 操作符查询JSON键
SELECT info -> 'customer' AS customer FROM orders;
PostgreSQL 允许将任何基本类型或用户定义类型创建为数组。
-- 定义数组列
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[], -- 一维整数数组
schedule text[][] -- 二维文本数组
);
-- 插入数组数据
INSERT INTO sal_emp VALUES (
'Bill',
'{10000, 10000, 10000, 10000}', -- 使用花括号
'{{"meeting", "lunch"}, {"training", "presentation"}}'
);
-- 查询:查找第二季度工资为10000的员工
SELECT name FROM sal_emp WHERE pay_by_quarter[2] = 10000;
用于表示某种元素类型的一个范围(或区间)。
类型名称 | 描述 | 示例 |
---|---|---|
INT4RANGE |
integer 的范围 |
[1, 10) |
INT8RANGE |
bigint 的范围 |
|
NUMRANGE |
numeric 的范围 |
|
TSRANGE |
timestamp 的范围 |
[2024-01-01 10:00, 2024-01-01 12:00] |
TSTZRANGE |
timestamptz 的范围 |
|
DATERANGE |
date 的范围 |
[2024-01-01, 2024-01-02) |
强大功能:支持查询重叠(&&
)、包含(@>
)、相交等操作。
CREATE TABLE reservation (room int, during TSRANGE);
INSERT INTO reservation VALUES (1108, '[2024-01-01 14:00, 2024-01-01 16:00)');
-- 查询哪些房间在给定时间范围内被预订
SELECT room FROM reservation WHERE during && '[2024-01-01 15:00, 2024-01-01 17:00)';
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
。BIT
, BIT VARYING
):存储 1 和 0 的串。TSVECTOR
, TSQUERY
):支持全文搜索。PostgreSQL 的数据类型系统是其最强大的特性之一。选择原则是:
INET
, JSONB
, UUID
)以获得更好的性能和功能。TEXT
**。SERIAL
或 IDENTITY
列。NUMERIC
。TIMESTAMPTZ
。