PostgreSQL - 自动递增

PostgreSQL - 自增列

创建一个自动为每行生成唯一数字的列是常见需求，特别是对于主键。PostgreSQL 为此提供了两种主要机制：现代的 SQL 标准 IDENTITY 列，以及传统的、PostgreSQL 特有的 SERIAL 伪类型。

最佳实践：对于新应用程序（PostgreSQL v10+），强烈建议使用 IDENTITY 列，因为它们更健壮且遵循 SQL 标准。

现代方法：IDENTITY 列

IDENTITY 约束在 PostgreSQL 10 中引入，是创建自增列的标准方式。它将序列生成器附加到列上，使其比旧的 SERIAL 类型更明确且可配置。

语法

CREATE TABLE table_name (
    column_name BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
    -- or GENERATED BY DEFAULT AS IDENTITY
);

有两种生成选项：

• GENERATED ALWAYS AS IDENTITY：这是更严格且通常更安全的选项。数据库将始终生成一个值。尝试手动 INSERT 值到此列将导致错误，除非您明确覆盖它。
• GENERATED BY DEFAULT AS IDENTITY：这更灵活。数据库仅在 INSERT 语句中未提供值时才生成值。这对于数据迁移很有用，但如果管理不当可能会导致冲突。

示例

CREATE TABLE employees (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL
);

-- 在 INSERT 语句中无需指定 'id' 列。
INSERT INTO employees (first_name, last_name) VALUES ('Alice', 'Smith');
INSERT INTO employees (first_name, last_name) VALUES ('Bob', 'Johnson');

生成的表将具有自动生成的 id 值：

 id | first_name | last_name
----+------------+-----------
  1 | Alice      | Smith
  2 | Bob        | Johnson
(2 rows)

传统方法：SERIAL 类型

在版本 10 之前，SERIAL 是 PostgreSQL 中的标准方法。它不是一个真正的数据类型，而是一种简写表示法，它会创建一个整型列、创建一个序列，并将该列的默认值设置为该序列的下一个值。

可用的类型有 SMALLSERIAL（2 字节）、SERIAL（4 字节）和 BIGSERIAL（8 字节）。BIGSERIAL 是主键最安全的选择，以避免用尽数字。

语法

CREATE TABLE legacy_products (
    id BIGSERIAL PRIMARY KEY,
    product_name TEXT NOT NULL
);

这实质上是以下内容的快捷方式：

CREATE SEQUENCE legacy_products_id_seq AS BIGINT;
CREATE TABLE legacy_products (
    id BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('legacy_products_id_seq'),
    product_name TEXT NOT NULL
);
ALTER SEQUENCE legacy_products_id_seq OWNED BY legacy_products.id;

选择 IDENTITY 还是 SERIAL

特性	IDENTITY (推荐)	SERIAL (旧版)
SQL 标准	是	否 (PostgreSQL 特有)
明确性	清晰明确的语法（GENERATED ... AS IDENTITY）。	作为一种神奇的伪类型，隐藏了底层序列。
权限	遵循标准列权限。	用户需要底层序列的 USAGE 权限，这可能被忽视。
覆盖行为	明确定义（ALWAYS 与 BY DEFAULT）。	行为类似于 BY DEFAULT，可以始终手动覆盖，这可能导致静默错误。

替代方案：使用 UUID

对于分布式系统，如果顺序 ID 可能导致冲突或泄露信息，使用全局唯一标识符（UUID）是一个健壮的替代方案。您可以使用 gen_random_uuid() 函数（来自 pgcrypto 扩展，通常默认启用）自动生成它们。

CREATE TABLE events (
    event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type TEXT NOT NULL,
    payload JSONB
);