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 管理生成策略,新表通常优先选择标识列。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用标识列

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通