第二节 PostgreSQL数据类型简介

亮子 2025-09-26 17:34:25 121 0 0 0

好的,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 位十进制数字精度

选择建议
* 主键、数量、年龄等用 SERIALINTEGER
* 金融、货币等要求精确计算的用 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 地址时,**强烈推荐使用 INETCIDR 而不是 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 的数据类型系统是其最强大的特性之一。选择原则是:

  1. 选择最精确、最小的类型以提高效率和减少存储。
  2. 善用特殊类型(如 INET, JSONB, UUID)以获得更好的性能和功能。
  3. 对于文本,若无严格长度要求,**首选 TEXT**。
  4. 对于自增主键,使用 SERIALIDENTITY 列。
  5. 对于精确计算(如金额),使用 NUMERIC
  6. 对于日期时间,优先使用 TIMESTAMPTZ