HOWTO · PostgreSQL
PostgreSQL 中的自动递增值
本文介绍如何在 PostgreSQL 中自动递增值。
本页内容
PostgreSQL 不使用 MySQL 的 AUTO_INCREMENT 列语法。对于自动生成的标识符,大多数新表应使用 identity 列;也可以使用较旧的 SERIAL 系列,或使用带有 nextval 的序列。这些机制会提供值,但本身并不保证唯一性。若要拒绝重复标识符,请添加 PRIMARY KEY 或 UNIQUE 约束。
为新表使用 Identity 列
identity 列由关联的序列提供支持。如果插入操作可能会提供自己的标识符,请将列声明为 GENERATED BY DEFAULT AS IDENTITY。如果通常应由 PostgreSQL 提供该值,并拒绝调用方提供的值,请使用 GENERATED ALWAYS AS IDENTITY。插入时省略 identity 列或使用 DEFAULT,这两种形式都会生成一个值。
使用完整语句创建表,然后在插入行时省略 identity 列:
CREATE TABLE cat (id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, color varchar NOT NULL);
INSERT INTO cat (color) VALUES ('black'), ('white'), ('brown'), ('tuxedo');
由于省略了 id,PostgreSQL 会为每一行生成一个值。具体起始值取决于 identity 序列的配置;如果需要知道某次插入分配的标识符,请使用 RETURNING id。这些 SQL 语句仅用于说明,本文没有在 PostgreSQL 服务器上运行它们。
BY DEFAULT 允许插入操作提供显式标识符;ALWAYS 则可防止生成的值被意外替换。两者的区别在于如何处理显式值,而不在于生成的值是否唯一。此例中的主键负责保证唯一性。
一个简短的 Python 模型演示了调用方省略标识符时如何分配下一个值。它只是一个模型,不会连接到 PostgreSQL,也不会复现数据库序列的行为。
运行此示例:
本节中的代码以及显示的标签或输出仍为英文。
next_id = 1
rows = []
for color in ["black", "white", "brown", "tuxedo"]:
rows.append((next_id, color))
next_id += 1
print(rows)
使用 SERIAL 或序列
SERIAL 和 BIGSERIAL 是 identity 列的简洁旧式替代方案。SERIAL 会创建一个整数列,其默认值取自关联序列;BIGSERIAL 的行为相同,但使用较大的整数类型。它们是在表定义中使用的伪类型,并非实际的整数类型。SERIAL 使用 4 字节整数,BIGSERIAL 使用 8 字节整数。序列不会自动将该列设为主键;如有需要,请添加主键或唯一性约束。
对于 cat 表,id SERIAL PRIMARY KEY 和 color varchar NOT NULL 定义了自动生成的标识符,并对其施加唯一性约束。只指定 color 的插入操作(例如上面的语句)会获得序列值。序列值可能存在间隙:已回滚事务或后来失败的插入所消耗的值不会被重新使用。不要把序列值当作连续无缺口的行数。
如果需要直接配置或共享序列行为,可以使用显式创建的序列。例如,CREATE SEQUENCE cat_id_seq START WITH 100; 会创建一个从 100 开始的序列,而 id integer NOT NULL DEFAULT nextval('cat_id_seq') 会使用它的下一个值。nextval 会推进序列;它不是随机生成器。手动创建序列时,还应考虑其所有权和生命周期:ALTER SEQUENCE cat_id_seq OWNED BY cat.id; 会将序列关联到该列,使其能够随该列一同管理。
向 Identity 列插入显式值
使用 GENERATED BY DEFAULT AS IDENTITY 时,该行提供的标识符优先。使用 GENERATED ALWAYS AS IDENTITY 时,PostgreSQL 通常会拒绝显式值。若要有意载入此类值,可使用 OVERRIDING SYSTEM VALUE,例如:INSERT INTO cat (id, color) OVERRIDING SYSTEM VALUE VALUES (20, 'black');。此子句会告知 PostgreSQL 在这次插入中使用提供的值。
显式值不会自动推进 identity 关联的序列。如果手动插入的值可能与之后生成的值重叠,请在恢复自动生成插入之前调整序列。日常插入时,应省略 identity 列,让 PostgreSQL 选择值。可使用 RETURNING id 获取生成的标识符,不要尝试预测下一个值;并发事务和序列间隙都会使预测变得不可靠。
为现有表添加自动生成的值
对于现有表,可使用 ALTER TABLE cat ADD COLUMN id integer GENERATED BY DEFAULT AS IDENTITY; 添加 identity 列。添加列时,PostgreSQL 会为已有行提供 identity 值,但该语句不会将此列设为主键。只有在确认数据满足主键约束,且表中尚无其他主键后,才添加主键。
若要为现有的普通列设置由序列提供支持的默认值,可以创建序列并设置列默认值,例如先执行 CREATE SEQUENCE cat_id_seq;,再执行 ALTER TABLE cat ALTER COLUMN id SET DEFAULT nextval('cat_id_seq');。这会影响省略 id 的后续插入;它不会填充现有的空值,也不会确保已有值唯一。在依赖自动生成的值之前,应将序列初始化到高于表中当前最大标识符的位置,同时考虑表为空时的情况和所需的起始值。如果应用需要这些保证,再添加 NOT NULL 或唯一性约束。标准的自动生成标识符行为可选用 identity;如果要维护现有的序列设计,或需要直接配置序列,则可使用 SERIAL 或显式序列。