PostgreSQL / 系统课时
使用标识列
使用标识列
本课解决建表时如何声明自动生成主键的问题。你将使用 GENERATED ... AS IDENTITY 创建标识列,验证自动编号和重启编号,并理解它与底层序列及旧式 serial 写法的关系。
学习目标
- 使用标识列定义自动生成主键
- 区分
GENERATED ALWAYS与BY DEFAULT - 调整标识列下一次生成的编号
标识列与序列的关系
标识列是 SQL 标准化的自动编号声明。PostgreSQL 会为它创建并管理关联序列,但序列属于列定义的一部分,删除列或表时更容易保持对象生命周期一致。
GENERATED ALWAYS 默认拒绝普通插入语句显式提供编号;GENERATED BY DEFAULT 允许显式值覆盖自动值。主键通常优先使用 ALWAYS,只有数据迁移或外部编号兼容时才考虑 BY DEFAULT。
创建并使用标识列
先运行 psql -d <DATABASE>,再执行以下完整脚本。
sql
DROP TABLE IF EXISTS public.customer_identity;
CREATE TABLE public.customer_identity (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
INSERT INTO public.customer_identity (name)
VALUES ('张敏'), ('刘洋');
ALTER TABLE public.customer_identity
ALTER COLUMN id RESTART WITH 10;
INSERT INTO public.customer_identity (name)
VALUES ('孙浩');
SELECT id, name
FROM public.customer_identity
ORDER BY id;
SELECT pg_get_serial_sequence(
'public.customer_identity',
'id'
) AS identity_sequence;预期结果
text
id | name
----+------
1 | 张敏
2 | 刘洋
10 | 孙浩
(3 rows)
identity_sequence
-------------------------------------
public.customer_identity_id_seq
(1 row)RESTART WITH 10 只改变后续生成值,不会修改已有记录。实际序列名受表名、列名和模式影响,应通过系统函数查询,不要在业务代码中硬编码猜测。
显式写入编号的边界
对于 GENERATED ALWAYS,普通插入显式编号会报错。迁移数据确需保留原编号时,可以在插入语句中使用 OVERRIDING SYSTEM VALUE,但必须自行保证唯一性,并在迁移后重新调整标识列起始值。
标识列只负责生成值,不自动等同于主键或唯一约束。本例显式添加了 PRIMARY KEY,实际设计中也应保留这一约束。
与 serial 的选择
serial 是 PostgreSQL 的历史简写,会展开为整数列、序列和默认值。标识列语义更明确、符合 SQL 标准,也更方便通过 ALTER TABLE 管理生成策略,新表通常优先选择标识列。
按自己的节奏,确认真正理解。