PostgreSQL 数据类型

数值类型

数值类型用于存储各种精度的数字数据。PostgreSQL 提供了多种整数和浮点数类型，以适应不同的精度和范围需求。

类型	描述	存储大小	取值范围
smallint	2 字节整数类型	2 字节	-32,768 到 32,767
integer	4 字节整数类型（默认整数类型）	4 字节	-2,147,483,648 到 2,147,483,647
bigint	8 字节整数类型	8 字节	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
decimal(p, s)	用户定义精度 (p) 和标度 (s) 的数值类型	可变	精度高达 38 位，范围约为 -10^38 到 10^38
numeric(p, s)	与 decimal 类似，表示精确数值，用户定义精度和标度	可变	精度高达 38 位，范围约为 -10^38 到 10^38
real	4 字节单精度浮点数	4 字节	约 ±1.701411734 × 10^38
double precision	8 字节双精度浮点数	8 字节	约 ±1.7976931348623157 × 10^308
serial	自动递增的 4 字节整数，常用于主键	4 字节	同 integer
bigserial	自动递增的 8 字节整数，用于大规模主键	8 字节	同 bigint

技术说明：

decimal 和 numeric 在 PostgreSQL 中是等同的，它们提供精确的数学计算，非常适合金融场景，避免浮点数误差。
serial 和 bigserial 并非真正的数据类型，而是创建自增列的快捷方式，底层通过序列 (sequence) 实现。

-- 示例：创建包含不同数值类型的表
CREATE TABLE financial_records (
    id serial PRIMARY KEY,              -- 自增主键
    amount_numeric numeric(10, 2),      -- 精确金额，保留两位小数
    quantity integer,                   -- 普通整数数量
    large_id bigserial,                 -- 大范围自增 ID
    ratio double precision              -- 高精度比率
);

整数类型根据范围需求选择 smallint、integer 或 bigint。
需要精确计算（如货币）时，务必使用 numeric 或 decimal。
serial 类型是生成唯一主键的便捷工具。

货币类型

PostgreSQL 提供了一种专门的货币类型，用于处理带有货币符号的固定小数值。

类型	描述	存储大小	取值范围
money	用于存储货币值的定点类型	8 字节	-2,147,483,648.00 到 2,147,483,647.00

注意： money 类型的输出格式依赖于数据库服务器的区域设置 (Locale)。在现代应用开发中，通常更推荐使用 numeric 类型来存储金额，并在应用层进行格式化，以获得更好的跨平台兼容性和控制力。

money 类型自动处理货币格式，但受区域设置影响较大。
对于需要严格控制的金融系统，numeric 通常是更优的选择。

字符类型

字符类型用于存储文本数据。PostgreSQL 提供了定长和变长两种主要模式。

类型	描述	存储大小	最大长度	示例值
char(n)	定长字符类型。如果字符串短于 n，则用空格填充	n + 1 字节	1 到 8,000	'A', 'Hello' (带填充空格)
varchar(n)	变长字符类型。可存储最大长度为 n 的字符串	1 + 实际长度	1 到 8,000	'Alice', 'Bob'
text	变长字符类型，无特定长度限制	1 + 实际长度	无限制	'这是一段很长的文本字符串。'

最佳实践：
在现代 PostgreSQL 版本中，text 和 varchar 在性能上几乎没有区别。除非有严格的长度限制需求，否则推荐优先使用 text 类型，因为它更加灵活且无需担心截断问题。char(n) 由于会自动填充空格，可能在比较操作时产生意外结果，使用时需谨慎。

-- 示例：用户信息表
CREATE TABLE users (
    username varchar(50) NOT NULL,   -- 限制用户名长度
    bio text,                        -- 简介，长度不限
    gender char(1)                   -- 性别代码，固定长度
);

text 类型通常优于 varchar，除非有明确的长度约束需求。
避免滥用 char(n)，注意其空格填充特性可能带来的逻辑陷阱。

二进制类型

二进制类型用于存储原始二进制数据，如图片、音频、视频或其他非文本文件。

类型	描述	存储大小	最大长度	示例值
bytea	存储二进制数据（字节数组）	可变	无限制	'\xDEADBEEF', '\x00FF'

bytea 类型允许以十六进制或转义格式存储数据。它是处理文件流、加密密钥或序列化对象的理想选择。

-- 示例：存储用户头像的二进制数据
CREATE TABLE user_avatars (
    user_id integer PRIMARY KEY,
    avatar_data bytea
);

-- 插入示例 (十六进制格式)
INSERT INTO user_avatars VALUES (1, '\x89504E470D0A1A0A');

bytea 是 PostgreSQL 中存储二进制大对象的标准类型。
支持十六进制和转义两种输入格式。

日期/时间类型

PostgreSQL 拥有强大的日期和时间处理能力，支持多种 temporal 数据类型，适用于事件调度、日志记录和歷史数据分析。

类型	描述	存储大小	取值范围	示例值
date	存储日历日期（年、月、日）	4 字节	公元前 4713 年到公元 5874897 年	2024-10-08
time	存储一天中的时间（时、分、秒）	8 字节	00:00:00 到 24:00:00 (可带时区)	13:45:30, 12:00:00
timestamp	存储日期和时间（无时区）	8 字节	公元前 4713 年到公元 5874897 年	2024-10-08 14:30:00
timestamptz	存储日期和时间（带时区）	8 字节	公元前 4713 年到公元 5874897 年	2024-10-08 14:30:00-05
interval	表示一段时间跨度（天、小时、分、秒）	可变	-178000 年到 178000 年	1 day, 2 hours 30 mins

关键概念：

timestamp 与 timestamptz 的区别：timestamptz 会将输入的时间转换为 UTC 存储，并在查询时根据会话时区显示。这是处理多时区应用的首选。
interval 类型非常灵活，可以进行加减运算，例如计算两个时间点之间的差值。

-- 示例：事件日志表
CREATE TABLE event_logs (
    event_id serial PRIMARY KEY,
    event_date date,
    start_time time,
    created_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 自动记录创建时间
    duration interval
);

-- 查询：计算持续时间超过 1 小时的事件
SELECT * FROM event_logs WHERE duration > INTERVAL '1 hour';

处理全球应用时，始终优先使用 timestamptz。
interval 类型让时间差的计算变得直观且强大。

布尔类型

布尔类型用于存储真值，是条件判断和逻辑控制的基础。

类型	描述	存储大小	示例值
boolean	存储 true、false 和 null 值	1 字节	true, false, NULL

PostgreSQL 的布尔类型非常智能，支持多种文本输入格式（如 't', 'f', 'yes', 'no', 'on', 'off' 等），但在输出时统一规范为 true 或 false。

布尔类型仅占用 1 字节，效率极高。
支持丰富的输入别名，增强可读性。

枚举类型

枚举类型 (Enums) 允许用户定义一组静态、有序的值。当某列只能从有限的预定义集合中取值时，枚举类型能极大地增强数据完整性和可读性。

类型	描述	存储大小	示例值
enum	用户定义的类型，由静态有序的值集合组成	可变	'small', 'medium', 'large'

使用枚举可以防止无效数据的插入，并使代码意图更加清晰。

-- 定义枚举类型
CREATE TYPE shirt_size AS ENUM ('small', 'medium', 'large', 'xlarge');

-- 在表中使用
CREATE TABLE products (
    product_id serial PRIMARY KEY,
    size shirt_size
);

-- 有效插入
INSERT INTO products (size) VALUES ('medium');

-- 无效插入 (将报错)
-- INSERT INTO products (size) VALUES ('huge');

枚举类型限制了列的取值范围，保证了数据一致性。
修改枚举类型（添加新值）需要特定的 DDL 操作。

几何类型

PostgreSQL 内置了二维几何数据类型，适用于地理信息系统 (GIS)、计算机图形学和几何建模等场景。

类型	描述	存储大小	示例值
point	表示二维空间中的点 (x, y)	16 字节	(1, 2), (3.5, 4.5)
line	表示二维空间中的无限直线	32 字节	line(1, 2)
lseg	表示二维空间中的线段	32 字节	lseg((1, 2), (3, 4))
box	表示二维空间中的矩形框	32 字节	box((1, 2), (3, 4))
path	表示二维空间中的路径（开放或封闭）	可变	path((1, 2), (3, 4), (5, 6))
polygon	表示二维空间中的多边形	可变	polygon((1, 1), (2, 2), (1, 2))
circle	表示二维空间中的圆 (圆心，半径)	32 字节	circle((1, 1), 5)

提供了一套完整的二维几何对象模型。
支持复杂的几何运算和空间关系判断。

其他专用数据类型

为了满足现代应用、网络编程和文档存储的需求，PostgreSQL 还提供了一系列专用数据类型。

数据类型	描述	存储大小	示例
UUID	存储 128 位通用唯一标识符	16 字节	550e8400-e29b-41d4-a716-446655440000
JSON	以文本形式存储 JSON 数据	可变	{"name":"Riya","age":21}
JSONB	以二进制优化格式存储 JSON	可变	{"price":120,"stock":true}
INET	存储 IPv4 或 IPv6 地址	7 或 19 字节	192.168.1.1
CIDR	存储网络块	7 或 19 字节	192.168.0.0/24
MACADDR	存储 MAC 地址	6 字节	08:00:2b:01:02:03
TSVECTOR	存储全文搜索文档	可变	'postgres database'

JSONJSONB: 保持输入的文本格式，写入快但查询慢
JSONB: 将其解析为二进制结构，支持索引，查询性能极佳，是现代应用的首选
UUID: 常用于分布式系统中生成全局唯一的主键，避免单点冲突
INET/CIDR: 内置了 IP 地址验证和网络包含运算功能，比纯文本存储更安全高效

-- 示例：存储用户配置和网络信息
CREATE TABLE app_settings (
    user_id UUID PRIMARY KEY,
    preferences JSONB,          -- 可索引的配置项
    ip_address INET,
    search_vector TSVECTOR      -- 用于全文检索
);

-- 创建 GIN 索引以加速 JSONB 查询
CREATE INDEX idx_preferences ON app_settings USING GIN (preferences);

JSONB 是处理半结构化数据的强力工具，支持索引和高效查询。
网络类型 (INET, CIDR) 提供了原生的 IP 地址处理能力。
TSVECTOR 是构建高性能全文搜索引擎的核心组件。