PostgreSQL - 数据类型

PostgreSQL - 现代数据类型

在本章中，我们将深入探讨 PostgreSQL 中丰富的数据类型。在创建表时，您必须为每个列指定一个数据类型。这定义了该列可以容纳的数据种类，例如整数、文本或日期。

正确定义数据类型提供了以下几个关键优势：

• 数据完整性：确保只存储有效数据。例如，DATE（日期）列会拒绝无效的日期格式。
• 性能：使用最合适的数据类型可以使 PostgreSQL 高效存储数据并更快地处理查询。
• 一致性：对相同类型列的操作会产生可预测且一致的结果。
• 功能性：解锁各种类型特定的函数和运算符（例如，日期算术、JSON 操作）。

PostgreSQL 提供了一套全面的标准 SQL 数据类型。此外，它还允许用户使用 CREATE TYPE 命令定义自己的自定义数据类型，提供了极大的可扩展性。

数值类型

数值类型用于存储定量数据。它们包括各种大小的整数、浮点数和精确小数。

名称	存储大小	描述	范围
smallint	2 字节	小范围整数	-32768 到 +32767
integer	4 字节	整数的标准选择	-2147483648 到 +2147483647
bigint	8 字节	用于非常大数字的大范围整数	-9223372036854775808 到 9223372036854775807
numeric(p, s), decimal(p, s)	可变	用户指定精度，精确。最适合财务数据。	小数点前最多 131072 位数字，小数点后最多 16383 位数字。
real	4 字节	可变精度，不精确，单精度	6 位小数精度
double precision	8 字节	可变精度，不精确，双精度	15 位小数精度

自增类型（标识列）

为了创建自增主键，现代的 SQL 标准方法是使用标识列（identity columns）。较旧的 serial 类型仍然受支持，但现在被认为是遗留（legacy）类型。

CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name TEXT NOT NULL
);

-- 较旧的 'serial' 语法等同于：
CREATE TABLE products_legacy (
    product_id SERIAL PRIMARY KEY, -- This is older syntax
    product_name TEXT NOT NULL
);

优先使用 GENERATED ALWAYS AS IDENTITY 而不是 SERIAL，因为它更符合标准，并能防止用户意外地插入或更新标识列。

货币类型

虽然 PostgreSQL 提供了 money 类型，但通常不建议在现代应用程序中使用它。其行为依赖于地域设置，并且在计算过程中可能出现舍入误差。对于财务和货币数据，请始终使用提供精确度的 NUMERIC 或 DECIMAL 类型。

-- 最佳实践：使用 NUMERIC 存储货币
CREATE TABLE transactions (
    transaction_id INT GENERATED ALWAYS AS IDENTITY,
    amount NUMERIC(10, 2) NOT NULL -- e.g., for amounts up to 99,999,999.99
);

字符类型

这些类型用于存储文本字符串。

名称	描述
character varying(n), varchar(n)	带有最大长度限制的可变长度字符串。
character(n), char(n)	固定长度字符串，不足指定长度时用空格填充。
text	没有预定义长度限制的可变长度字符串。

最佳实践：在现代 PostgreSQL 中，varchar(n) 和 text 之间没有性能差异。通常建议默认使用 text，除非您需要针对数据验证（例如，一个两字符的国家代码）的特定长度约束，在这种情况下，您可以使用 varchar(2) 或添加 CHECK 约束。

二进制数据类型

bytea 数据类型用于存储二进制数据，例如图像、音频文件或其他非文本信息。

名称	描述
bytea	可变长度二进制字符串。

日期/时间类型

PostgreSQL 拥有一套强大的日期和时间处理类型。除了 date 类型，所有类型都具有微秒精度。

名称	存储大小	描述
timestamp [without time zone]	8 字节	日期和时间。使用时需谨慎，因为它不识别时区。
timestamptz [with time zone]	8 字节	日期和时间，带有时区感知。强烈推荐。
date	4 字节	仅日期（无时间）。
time [without time zone]	8 字节	仅时间（无日期）。
timetz [with time zone]	12 字节	仅时间，带有时区感知。
interval	16 字节	表示时间段（例如，‘2 天’ 或 ‘3 小时’）。

最佳实践：几乎总是使用 timestamptz 而不是 timestamp。timestamptz 在内部以 UTC 存储时间戳，并在检索时将其转换为客户端的会话时区，从而避免了与时区相关的歧义和错误。

布尔类型

标准的 SQL boolean 类型可以包含三种状态之一：true（真）、false（假）或 NULL（表示“未知”状态）。有效的字面值包括 TRUE、FALSE、't'、'f'、'1' 和 '0'。

名称	存储大小	描述
boolean	1 字节	真、假或空的逻辑状态。

枚举类型 (ENUM)

枚举类型是自定义类型，包含一个静态的、有序的字符串标签集合。它们对于表示必须是预定义值列表之一的值（例如状态码或类别）非常有用。

-- 首先，创建类型
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- 然后，在表定义中使用它
CREATE TABLE orders (
    order_id INT GENERATED ALWAYS AS IDENTITY,
    status order_status NOT NULL DEFAULT 'pending'
);

与为此目的使用带有 CHECK 约束的简单 text 列相比，枚举类型更高效且类型更安全。

几何类型

几何类型表示二维空间对象。虽然它们对于基本的几何计算很有用，但对于高级地理空间分析，PostGIS 扩展是行业标准，应优先使用。

名称	表示	描述
point	(x,y)	平面上的一个点
lseg	((x1,y1),(x2,y2))	有限线段
box	((x1,y1),(x2,y2))	矩形框
path	((x1,y1),…)	闭合路径（类似于多边形）
polygon	((x1,y1),…)	多边形
circle	<(x,y),r>	中心点和半径

网络地址类型

PostgreSQL 提供了专门用于存储网络地址的类型，提供了验证和类型特定的运算符。

名称	存储大小	描述
inet	7 或 19 字节	存储 IPv4 或 IPv6 主机地址，可选带子网。
cidr	7 或 19 字节	以 CIDR 表示法存储 IPv4 或 IPv6 网络地址。
macaddr	6 字节	存储 6 字节 MAC 地址 (EUI-48)。
macaddr8	8 字节	存储 8 字节 MAC 地址 (EUI-64)。在 PostgreSQL 10 中添加。

JSON 类型

PostgreSQL 为存储和查询 JSON 数据提供了强大的支持。有两种类型：json 和 jsonb。

• json：存储输入文本的精确副本。插入速度更快，但处理速度较慢，因为每次操作都必须重新解析。
• jsonb：以分解的二进制格式存储 JSON。由于转换开销，插入速度稍慢，但查询速度显著更快。它还支持索引。jsonb 是几乎所有用例的推荐类型。

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    profile_data JSONB
);

INSERT INTO user_profiles (user_id, profile_data)
VALUES (1, '{"name": "Alice", "tags": ["dev", "sql"], "active": true}');

-- 使用特定运算符查询 JSONB 数据
SELECT profile_data -> 'name' AS user_name
FROM user_profiles
WHERE profile_data ->> 'active' = 'true';

数组类型

PostgreSQL 中的任何数据类型都可以用于创建可变长度的多维数组。数组对于在单个列中存储值列表很有用。

CREATE TABLE posts (
    post_id INT GENERATED ALWAYS AS IDENTITY,
    title TEXT,
    tags TEXT[], -- 一维文本数组
    visitor_scores INT[] -- 一维整数数组
);

-- 使用现代 ARRAY 构造函数插入数据
INSERT INTO posts (title, tags)
VALUES ('PostgreSQL is Awesome', ARRAY['sql', 'database', 'postgres']);

-- 在数组中搜索特定元素
SELECT title, tags FROM posts WHERE 'sql' = ANY(tags);

UUID 类型

UUID（Universally Unique Identifier，通用唯一标识符）是一个 128 位的值，用于在计算机系统中唯一标识信息。它是分布式系统中主键的绝佳选择。例如 a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11。

要生成 UUID，您可以使用 gen_random_uuid() 函数（在 PostgreSQL 13+ 中可用），或在旧版本中为 gen_random_uuid() 函数启用 pgcrypto 扩展。

-- 适用于 PostgreSQL 13+
CREATE TABLE documents (
    doc_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT
);

其他专用类型

PostgreSQL 还包括许多其他专用类型：

• XML 类型： 用于存储 XML 数据，带有验证和 XPath 查询函数。
• 范围类型： 表示值的范围，例如 int4range（整数范围）或 daterange（日期范围）。对调度和基于时间的逻辑很有用。
• 文本搜索类型： tsvector 和 tsquery 用于实现强大的全文搜索功能。
• 复合类型： 表示行结构的一种类型，将多个字段捆绑到一个列中。常用于函数的返回类型。

已废弃的类型

对象标识符类型 (OIDs)： 在 PostgreSQL 的旧版本中，OIDs 被用作表的内部主键。在用户表中添加 oid 列的特性（WITH OIDS）现已废弃，并已从 PostgreSQL 12 中移除。您应该始终使用 INTEGER、BIGINT 或 UUID 等类型为您的表定义显式主键。