好的，PostgreSQL 以其丰富且强大的数据类型系统而闻名，远远超出了标准 SQL 类型。它支持各种基础类型、结构化类型、几何网络类型，甚至允许创建自定义类型。
以下是 PostgreSQL 中主要数据类型的详细分类和说明：
一、数值类型（Numeric Types）
用于存储数字，分为三类：
| 类型名称 | 存储大小 | 描述 | 范围 |
|---|---|---|---|
| 整数类型 | |||
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 Types）
用于存储文本。
| 类型名称 | 描述 | 特点 |
|---|---|---|
CHARACTER(n) (CHAR(n)) |
定长字符串 | 不足长度用空格填充，**浪费空间**，适用于长度固定的代码（如国家代码、MD5哈希）。 |
CHARACTER VARYING(n) (VARCHAR(n)) |
变长字符串 | 最常用。只存储实际字符串，有长度限制。 |
TEXT |
变长字符串 | 更常用。无限长度（实际受限于最大字段大小 1GB）。性能与 VARCHAR 无显著差异，通常首选 TEXT。 |
选择建议：**无特殊要求，直接用 TEXT**。需要强制业务长度限制时用 VARCHAR(n)。
三、日期/时间类型（Date/Time Types）
用于存储时间点或时间间隔。
| 类型名称 | 存储大小 | 描述 | 示例 |
|---|---|---|---|
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 Type）
| 类型名称 | 存储大小 | 描述 |
|---|---|---|
BOOLEAN (BOOL) |
1 字节 | 逻辑布尔值 |
可输入值：TRUE, FALSE, NULL。也支持简化写法：t, f, yes, no, on, off, 1, 0。 |
五、网络地址类型（Network Address Types）
专门用于高效存储网络地址。
| 类型名称 | 存储大小 | 描述 | 优势 |
|---|---|---|---|
CIDR |
7 或 19 字节 | IPv4 或 IPv6 网络地址 | 有输入检查和专门的操作符（如 >> 包含） |
INET |
7 或 19 字节 | IPv4 或 IPv6 主机地址 | 可以存储网络地址和其子网掩码 |
MACADDR |
6 字节 | MAC 地址 | 有输入检查和专门的操作符 |
选择建议：存储 IP 地址时，**强烈推荐使用 INET 或 CIDR 而不是 TEXT**，因为它们有输入验证、更高效的存储和丰富的操作函数。
六、二进制数据类型（Binary Data Types）
用于存储二进制串（字节序列）。
| 类型名称 | 描述 | 优势 |
|---|---|---|
BYTEA |
变长二进制字符串 | PostgreSQL 的原生二进制类型 |
BLOB / Large Object |
另一种存储大对象的方式 | 通过一个 OID（对象标识符）在系统表 pg_largeobject 中存储，最大 4TB |
选择建议：存储图片、文件、压缩数据等，小于 1GB 用 BYTEA，极大文件用 Large Object。
七、枚举类型（Enumerated Types）
由一组静态、有序的值组成的数据类型，类似其他编程语言中的枚举。
-- 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'); -- 错误！
八、几何类型（Geometric Types）
用于存储平面上的几何图形。
| 类型名称 | 存储大小 | 描述 | 表示方法 |
|---|---|---|---|
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> |
九、JSON 类型（JSON Types）
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;
十、数组类型（Array Types）
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;
十一、范围类型（Range Types）
用于表示某种元素类型的一个范围（或区间）。
| 类型名称 | 描述 | 示例 |
|---|---|---|
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)';
十二、其他特殊类型
- UUID：存储全局唯一标识符（UUID），例如
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11。 - XML：存储 XML 数据。
- 位串类型 (
BIT,BIT VARYING)：存储 1 和 0 的串。 - 文本搜索类型 (
TSVECTOR,TSQUERY)：支持全文搜索。 - 复合类型：创建自定义的字段组合，类似于一个简单的行或结构体。
- 域类型：基于现有类型创建一个带有约束的新类型。
总结
PostgreSQL 的数据类型系统是其最强大的特性之一。选择原则是：
- 选择最精确、最小的类型以提高效率和减少存储。
- 善用特殊类型（如
INET,JSONB,UUID）以获得更好的性能和功能。 - 对于文本，若无严格长度要求，**首选
TEXT**。 - 对于自增主键，使用
SERIAL或IDENTITY列。 - 对于精确计算（如金额），使用
NUMERIC。 - 对于日期时间，优先使用
TIMESTAMPTZ。