主键是用来标识每一行的一列或一组列。它的值必须唯一且不能为 NULL,一张表只能有一个主键。在 PostgreSQL 中,通常用标识列让数据库自动生成主键:id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY。
id 分别是 1 和 2。定义主键的同时还会在上面创建一个唯一索引,名字是 users_pkey。
标识列:ALWAYS 和 BY DEFAULT
标识列是 SQL 标准中给行编号的方式,从 PostgreSQL 10 开始支持。它有两种形式:
GENERATED ALWAYS AS IDENTITY会拒绝INSERT中显式给出的值,除非插入语句写了OVERRIDING SYSTEM VALUE。这是更安全的默认选择。GENERATED BY DEFAULT AS IDENTITY只在你没有提供值时才使用生成的值。
使用 BY DEFAULT 时,自己插入 id 会让序列落在后面,下一个生成的 id 就会冲突。导入带有显式 id 的行之后,用 setval 把序列推到最大 id 之后:
如果没有 setval 那一行,最后一次插入会得到 id 1,并因为主键重复而报错。有了它,新行得到的是 4。
SERIAL 和标识列
serial(4 字节)和 bigserial(8 字节)并不是真正的类型。它们是一种简写,会创建一个序列,并把列的默认值设为该序列的 nextval():
这一列是普通的 integer,默认值是 nextval('legacy_id_seq'::regclass)。没有什么能阻止插入语句自己写 id,而且序列还需要单独授权。标识列解决了这两个问题,所以新表优先用标识列。serial 仍然可以用,你会在旧的表结构和教程中见到它。
可能不断增长的表要用 bigint(或 bigserial)。integer 主键大约用到 21 亿个值就会耗尽。
UUID 主键
gen_random_uuid() 返回一个随机的第 4 版 UUID。从 PostgreSQL 13 开始它是内置的,不需要任何扩展:
每次运行打印的 id 都不同,文本形式每个 36 个字符。uuid 类型用 16 个字节存储它们,所以要用 uuid 而不是 text。PostgreSQL 18 新增了 uuidv7(),它生成按时间排序的 UUID,可以按索引顺序插入。在 PostgreSQL 17 中,这种 UUID 要在应用中生成。
复合主键
主键可以跨多列,在 CREATE TABLE 中单独写一行。它是关联表天然的主键,每一对值只能出现一次:
重复的 (1, 10) 被跳过了,所以计数是 3。要给已有的表添加主键,用 ALTER TABLE t ADD PRIMARY KEY (col)。其他修改见 ALTER TABLE 页面。其他表通过外键指向这个主键。
常见问题
PostgreSQL 有 AUTO_INCREMENT 吗?
没有这个名字。标准做法是标识列 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,从 PostgreSQL 10 开始支持。较早的 serial 和 bigserial 类型通过序列和默认值实现同样的效果。
为什么 PostgreSQL 的 id 序列会有空缺?
即使插入后来失败了,或者它所在的事务回滚了,序列也已经分配出了一个值,而且这个值永远不会再用。空缺是正常且无害的,所以不要指望 id 是连续的。
PostgreSQL 的主键会创建索引吗?
会。主键(以及每个 UNIQUE 约束)会自动创建一个唯一的 B-tree 索引,主键索引的名字是 tablename_pkey。
主键应该用 UUID 还是 bigint?
bigint 标识列主键更小(8 字节对 16 字节),而且按索引顺序插入。UUID 可以在任何地方生成,不需要往返数据库,也不会暴露表里有多少行。随机 UUID 会让插入分散在索引的各处,在大表上代价更高。