PHP小码农

Appearance

SERIAL 与序列管理 ​

上一节把序列对象与 nextval / currval / lastval / setval 讲完了，本节回答那个绕不开的问题：SERIAL 到底是什么。答案会让很多人意外——它不是一个数据类型，而是一组在解析阶段就被展开的 DDL 快捷方式。理解这一点，才能明白为什么 SERIAL 会留下孤儿序列、为什么它有 21 亿上限、以及为什么新项目应该改用 IDENTITY。

与「自增类型」的分工

自增类型（SERIAL / IDENTITY） 一章从数据类型与列定义出发，讲两种写法的语法、ALWAYS 与 BY DEFAULT 的行为差异、生成列 GENERATED ALWAYS AS (...) STORED 与 identity 的混淆点；本节从序列管理出发，把 SERIAL 拆成它真实的三段式展开，重点讲序列的所有权与生命周期、\d 与 pg_dump 里的真实形态、serial → identity 的迁移写法、AS bigint 扩容、以及权限与间隙的运维处理。四个序列函数（nextval / setval 等）的语义见 NEXTVAL / CURRVAL / LASTVAL / SETVAL。

前置知识

阅读本节前，建议先了解：NEXTVAL / CURRVAL / LASTVAL / SETVAL、自增类型（SERIAL / IDENTITY）

基础概念 ​

一句话概括本节最重要的结论：

SERIAL 不是类型，是语法糖。 它在词法分析阶段就被还原成三段独立的 DDL。

text
id SERIAL
   ↓ 展开为
id integer NOT NULL DEFAULT nextval('t_id_seq'::regclass)
                                   ↓ 外加
CREATE SEQUENCE t_id_seq OWNED BY t.id

三段分别对应三件事：

展开出的部分归属说明
integer列类型硬编码，无法通过 SERIAL 关键字改成 bigint
NOT NULL列约束SERIAL 隐含非空，不加也能通过，但展开一定带
DEFAULT nextval(...)列默认值逻辑上「借用」序列，不是列的一部分
CREATE SEQUENCE ... OWNED BY独立对象真正的号码机，有独立的权限、空间、生命周期

而 IDENTITY 是真正的列约束，序列被强绑定在列上，数据库知道它们的从属关系。

术语表：

术语含义
孤儿序列（Orphan Sequence）失去了所属列、但仍留在数据库里的序列
所有权（Ownership）OWNED BY 建立的从属关系，决定 DROP 时是否连带删除
弱绑定 / 强绑定SERIAL 的所有权可残留（弱），IDENTITY 随列删（强）
间隙（Gap）自增值不连续。回滚、缓存、冲突、崩溃都会留下空洞
序列耗尽（Exhaustion）类型上限到达后 nextval 报错，integer 序列为 2147483647

最小对照示例：

sql
CREATE TABLE t_a (id SERIAL PRIMARY KEY, name text);
CREATE TABLE t_b (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text);
text
CREATE TABLE
CREATE TABLE

两行的写法差异很小，但背后一个是「解析期重写」、一个是「标准列约束」。下面的 \d 输出能直接看出区别。

语法与用法 ​

text
-- 三种 SERIAL 写法（列类型被硬编码）
SMALLSERIAL     → smallint   + NOT NULL + DEFAULT nextval(...)
SERIAL          → integer    + NOT NULL + DEFAULT nextval(...)
BIGSERIAL       → bigint     + NOT NULL + DEFAULT nextval(...)

-- 对应的 IDENTITY 写法（类型自己写）
列名 类型 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

-- 存量表加自增（两种方式）
ALTER TABLE t ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
ALTER TABLE t ALTER COLUMN id SET DEFAULT nextval('some_seq'::regclass);

最小可运行示例：

sql
DROP TABLE IF EXISTS demo_serial;
CREATE TABLE demo_serial (
  id   SERIAL PRIMARY KEY,
  name text   NOT NULL
);

INSERT INTO demo_serial (name) VALUES ('a'), ('b') RETURNING id, name;
text
 id | name
----+------
  1 | a
  2 | b
(2 rows)

看起来很干净——但这份「干净」是解析器帮你做的，接下来要看清它背后到底建了什么。

详细说明 ​

\d 与 pg_dump 证明：SERIAL 不是类型 ​

psql 的 \d 输出会隐藏 SERIAL 的糖衣（这是它的贴心之处），所以要看真相必须查系统目录：

sql
-- 1) 列的真实类型是 integer，不是 serial
SELECT
  a.attname                            AS column_name,
  format_type(a.atttypid, a.atttypmod) AS real_type,
  pg_get_expr(d.adbin, d.adrelid)      AS default_expr,
  a.attnotnull                         AS not_null
FROM pg_attribute a
LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attrelid = 'demo_serial'::regclass
  AND a.attnum > 0
ORDER BY a.attnum;
text
 column_name | real_type |              default_expr              | not_null
-------------+-----------+----------------------------------------+----------
 id          | integer   | nextval('demo_serial_id_seq'::regclass) | t
 name        | text      |                                        | t
(2 rows)
sql
-- 2) 序列是被隐式创建的独立对象，且归属于该列
SELECT c.relname AS seq_name, d.deptype AS dependency_type
FROM pg_class c
JOIN pg_depend d ON d.objid = c.oid
WHERE c.relkind = 'S'
  AND d.refobjid = 'demo_serial'::regclass;
text
      seq_name      | dependency_type
--------------------+-----------------
 demo_serial_id_seq | a
(1 row)

dependency_type = 'a' 表示 AUTO 依赖——这是 OWNED BY 在系统目录里的体现。

pg_dump 的输出更能说明问题，因为它必须把糖衣还原成可移植的 DDL：

bash
pg_dump --schema-only --table=demo_serial demo
text
CREATE TABLE public.demo_serial (
    id integer NOT NULL,
    name text NOT NULL
);

CREATE SEQUENCE public.demo_serial_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.demo_serial_id_seq OWNED BY public.demo_serial.id;

ALTER TABLE ONLY public.demo_serial ALTER COLUMN id SET DEFAULT nextval('public.demo_serial_id_seq'::regclass);

这就是 SERIAL 的全部真相：一条 CREATE TABLE 在 dump 里变成了一条 CREATE TABLE + 一条 CREATE SEQUENCE + 一条 ALTER SEQUENCE OWNED BY + 一条 ALTER COLUMN SET DEFAULT。

SERIAL 与 IDENTITY 对比表 ​

对比项SERIALGENERATED ... AS IDENTITY
是否 SQL 标准否，PostgreSQL 私有是（SQL:2003 起）
本质解析期展开的三段 DDL真正的列约束
列类型固定 smallint / integer / bigint由你显式声明
序列所有权OWNED BY，删列时可能残留孤儿序列强绑定，随列一起删除
序列权限需单独 GRANT USAGE ON SEQUENCE随列权限自动处理（PostgreSQL 11+）
手工插入允许ALWAYS 需 OVERRIDING SYSTEM VALUE，BY DEFAULT 允许
能否多列共用一个序列能（手工 SET DEFAULT）不能，一列一序列
修改起始值ALTER SEQUENCE ... RESTART 或 setvalALTER TABLE ... ALTER COLUMN ... RESTART WITH n
pg_get_serial_sequence()支持支持
迁移可移植性仅 PostgreSQL标准语法，其他数据库有对应物
新项目推荐度兼容旧项目首选

关于权限这一条，PostgreSQL 11 之后 IDENTITY 不再要求单独给序列授权，这是它相对 SERIAL 的一个实际优势：

sql
-- SERIAL 场景：应用账号需要两个授权
GRANT INSERT ON public.demo_serial TO app_role;
GRANT USAGE  ON SEQUENCE public.demo_serial_id_seq TO app_role;

-- IDENTITY 场景：一个授权就够
GRANT INSERT ON public.demo_identity TO app_role;

漏掉第一条的 GRANT USAGE，插入时报的是让人一头雾水的错误：

text
ERROR:  permission denied for sequence demo_serial_id_seq

SERIAL 序列的所有权与生命周期 ​

OWNED BY 的效果是「删列（或删表）时连带删除序列」，反过来删序列不会删列：

sql
-- 删表，序列跟着走
DROP TABLE demo_serial;
SELECT count(*) AS leftover FROM pg_class WHERE relname = 'demo_serial_id_seq' AND relkind = 'S';
text
 leftover
----------
        0
(1 row)

但只删列时，行为就要小心了：

sql
CREATE TABLE t_drop_col (id SERIAL, v text);
ALTER TABLE t_drop_col DROP COLUMN id;

-- 序列仍然存在，成为孤儿序列
SELECT c.relname
FROM pg_class c
WHERE c.relkind = 'S' AND c.relname = 't_drop_col_id_seq';
text
       relname
--------------------
 t_drop_col_id_seq
(1 row)

这是 SERIAL 常被诟病的一点：删列不清序列。定期巡检孤儿序列是必要的运维动作：

sql
-- 找出没有任何依赖关系的序列（孤儿）
SELECT c.relname AS orphan_sequence
FROM pg_class c
WHERE c.relkind = 'S'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_depend d
    WHERE d.objid = c.oid
      AND d.deptype IN ('a', 'i')
  )
ORDER BY c.relname;

serial → identity 迁移写法 ​

存量表想从 SERIAL 换成 IDENTITY，可以复用原有序列，不必重新分配 id：

sql
DROP TABLE IF EXISTS migrate_demo;
CREATE TABLE migrate_demo (id serial PRIMARY KEY, name text);
INSERT INTO migrate_demo (name) SELECT 'r' || g FROM generate_series(1, 5) AS g;

-- 第一步：摘掉原有的 DEFAULT
ALTER TABLE migrate_demo ALTER COLUMN id DROP DEFAULT;

-- 第二步：挂上 identity，并让它接管原有序列
ALTER TABLE migrate_demo
  ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY
  ( SEQUENCE NAME migrate_demo_id_seq );
text
ALTER TABLE
ALTER TABLE
sql
-- 验证：序列名没变，值继续往下走
SELECT pg_get_serial_sequence('migrate_demo', 'id') AS seq_name;
SELECT pg_sequences.last_value, pg_sequences.is_called
FROM pg_sequences WHERE sequencename = 'migrate_demo_id_seq';

INSERT INTO migrate_demo (name) VALUES ('after-migration') RETURNING id;
text
       seq_name
----------------------
 public.migrate_demo_id_seq
(1 row)

 last_value | is_called
------------+-----------
          5 | t
(1 row)

 id
----
  6
(1 row)

用 BY DEFAULT 而非 ALWAYS 是关键：存量表上云、跨环境导入数据时都要能手写 id，ALWAYS 会让导入脚本全部报错。

SERIAL 的 2147483647 上限与 AS bigint 扩容 ​

SERIAL 的列类型是 integer，序列类型也是 integer，上限 2147483647。一旦到达：

text
ERROR:  nextval: reached maximum value of sequence "demo_serial_id_seq" (2147483647)

修复分两步，顺序不能反——先把序列类型换成 bigint，再换列类型：

sql
-- 第一步：序列升为 bigint（PostgreSQL 10+）
ALTER SEQUENCE demo_serial_id_seq AS bigint;

-- 第二步：列类型升为 bigint
ALTER TABLE demo_serial ALTER COLUMN id TYPE bigint;
text
ALTER SEQUENCE
ALTER TABLE

两点注意：

  • ALTER SEQUENCE ... AS bigint 需要 PostgreSQL 10+，更早版本只能重建序列。
  • 换列类型会重写整张表并重建索引，大表上代价极高，务必在低峰期或用 pg_repack 之类的工具。
sql
-- 确认两边都换好了
SELECT
  a.attname, format_type(a.atttypid, a.atttypmod) AS col_type
FROM pg_attribute a
WHERE a.attrelid = 'demo_serial'::regclass AND a.attname = 'id'
UNION ALL
SELECT 'sequence'::name, data_type FROM pg_sequences WHERE sequencename = 'demo_serial_id_seq';
text
 attname  | col_type
----------+----------
 id       | bigint
 sequence | bigint
(2 rows)

新项目直接用 bigint GENERATED ALWAYS AS IDENTITY 可以从根本上避免这个问题。

pg_get_serial_sequence + setval 修复流程 ​

这是最高频的序列事故：手工插入了显式 id，序列没跟着走，之后自增插入撞主键。

sql
DROP TABLE IF EXISTS fix_demo;
CREATE TABLE fix_demo (id serial PRIMARY KEY, name text);

-- 手工导入历史数据，显式指定 id（序列完全不知情）
INSERT INTO fix_demo (id, name) VALUES (100, 'legacy-100'), (200, 'legacy-200');
text
INSERT 0 2
sql
-- 现在自增插入，序列还是从 1 开始 → 大概率撞已存在的键
INSERT INTO fix_demo (name) VALUES ('new');
text
 id  |   name
-----+----------
   1 | new
(1 row)

这里侥幸没冲突（1 未被占用），但把 100 抢在前面就会炸：

sql
-- 错误！序列推进到 100 时撞上已存在的行
INSERT INTO fix_demo (name) SELECT 'x' FROM generate_series(1, 100);
text
ERROR:  duplicate key value violates unique constraint "fix_demo_pkey"
DETAIL:  Key (id)=(100) already exists.

标准修复流程（pg_get_serial_sequence 负责反查序列名，避免硬编码）：

sql
SELECT pg_get_serial_sequence('fix_demo', 'id') AS seq_name;
text
        seq_name
------------------------
 public.fix_demo_id_seq
(1 row)
sql
-- 方式一：setval + max(id)，is_called = true（下次从 max+1 开始）
SELECT setval(
         pg_get_serial_sequence('fix_demo', 'id'),
         (SELECT max(id) FROM fix_demo)
       );

INSERT INTO fix_demo (name) VALUES ('after-fix') RETURNING id;
text
 setval
--------
    200
(1 row)

 id
-----
 201
(1 row)
sql
-- 方式二：显式写 false，下次从 max(id) 本身开始（当 max(id) 尚未使用时用）
SELECT setval(pg_get_serial_sequence('fix_demo', 'id'), (SELECT max(id) FROM fix_demo), false);
text
 setval
--------
    200
(1 row)

is_called 的取舍已在上一节详述：true 表示「这个值已经被取走」，下次给 max+1；false 表示「这个值还没用」，下次给 max 本身。手工导入数据后用 true 最安全。

对 IDENTITY 列，还有更直观的专用语法：

sql
ALTER TABLE fix_demo ALTER COLUMN id RESTART WITH 1000;
text
ALTER TABLE

生成列 GENERATED ALWAYS AS (...) STORED 与 identity 的区别 ​

两个特性都带 GENERATED，极易混淆，但它们解决的是完全不同的问题：

对比项GENERATED ... AS IDENTITYGENERATED ALWAYS AS (expr) STORED
名字身份列（Identity Column）生成列（Generated Column）
值的来源序列（跨行递增）同一行其他列的表达式
是否可跨行依赖是，值是全局递增的否，只依赖本行
是否可写ALWAYS 不可写；BY DEFAULT 可写完全不可写，写就报错
表达式中能否用 nextval()无关（identity 本身就是序列）不能，非不可变函数被拒
引入版本列约束形式 10+（增强 11/17）PostgreSQL 12+
典型用途主键自增冗余计算结果、规范化字段
sql
DROP TABLE IF EXISTS gen_demo;
CREATE TABLE gen_demo (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- 跨行递增
  price      numeric(10, 2) NOT NULL,
  qty        integer        NOT NULL,
  amount     numeric(12, 2) GENERATED ALWAYS AS (price * qty) STORED  -- 本行内计算
);

INSERT INTO gen_demo (price, qty) VALUES (12.50, 4) RETURNING id, amount;
text
 id | amount
----+--------
  1 |  50.00
(1 row)
sql
-- 错误！生成列不能显式写入
INSERT INTO gen_demo (price, qty, amount) VALUES (1, 1, 1);
text
ERROR:  cannot insert a non-DEFAULT value into column "amount"
DETAIL:  Column "amount" is a generated column.
sql
-- 错误！生成列表达式必须是不可变的，不能调用 nextval
CREATE TABLE bad_gen (id integer, x int GENERATED ALWAYS AS (nextval('some_seq')) STORED);
text
ERROR:  generation expression is not immutable

记忆法：IDENTITY 管「这一行是谁」，生成列管「这一行的这个值等于什么」。

用 DEFAULT 表达式替代序列的不可行性 ​

有人会想：既然自增只要一个不断变大的数，那用 DEFAULT (SELECT max(id) + 1 FROM t) 行不行？

不行，而且非常危险。

sql
DROP TABLE IF EXISTS no_seq_demo;
CREATE TABLE no_seq_demo (
  id     integer PRIMARY KEY DEFAULT (SELECT coalesce(max(id), 0) + 1 FROM no_seq_demo),
  name   text
);
text
CREATE TABLE

单线程插入看起来正常：

sql
INSERT INTO no_seq_demo (name) VALUES ('a') RETURNING id;
text
 id
----
  1
(1 row)

但两个会话同时插入就会撞主键：

text
-- 会话 1                          -- 会话 2
BEGIN;                             BEGIN;
INSERT ... ;  -- max = 1, 得到 2   INSERT ... ;  -- max 还是 1, 也得到 2
                                   INSERT ... ;  -- 错误！duplicate key

致命缺陷清单：

缺陷说明
并发不安全两个事务读出同一个 max，必然冲突
需额外加锁想安全就得 LOCK TABLE，把并发写彻底串行化
索引代价每次插入都要扫一遍索引求 max，插入变慢
删除后回退删掉最大行后，下一个 id 会重复使用已删的 id
权限更复杂默认值里查表需要额外的表权限

序列之所以存在，正是因为它用非事务性的原子递增绕开了这一切：不读表、不加锁、不冲突。代价是允许间隙——这个交换永远值得。

sql
-- 正确做法：让数据库管理序列
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

实战示例 ​

场景：把一张历史订单表从 SERIAL 迁移到 IDENTITY，导入存量数据，校正序列，并做一次完整的运维巡检。

第一步：建表并模拟历史数据

sql
DROP TABLE IF EXISTS orders;
DROP SEQUENCE IF EXISTS orders_id_seq;

CREATE TABLE orders (
  id         serial PRIMARY KEY,
  order_no   varchar(32) NOT NULL UNIQUE,
  amount     numeric(12, 2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
text
DROP TABLE
DROP SEQUENCE
CREATE TABLE

第二步：确认序列的真实归属

sql
SELECT
  pg_get_serial_sequence('orders', 'id')       AS seq_name,
  s.data_type,
  s.start_value,
  s.max_value,
  s.cache_size,
  s.cycle
FROM pg_sequences s
WHERE s.sequencename = 'orders_id_seq';
text
     seq_name      | data_type | start_value |      max_value      | cache_size | cycle
-------------------+-----------+-------------+---------------------+------------+-------
 public.orders_id_seq | integer |           1 |          2147483647 |          1 | f
(1 row)

第三步：导入带显式 id 的历史数据

sql
-- 模拟从 CSV 导入，id 由源系统提供
INSERT INTO orders (id, order_no, amount) VALUES
  (1001, 'SO-1001', 99.00),
  (1002, 'SO-1002', 150.00),
  (1003, 'SO-1003', 42.50);
text
INSERT 0 3

第四步：暴露问题——序列落后

sql
INSERT INTO orders (order_no, amount) VALUES ('SO-NEW', 10.00) RETURNING id, order_no;
text
 id | order_no
----+----------
  1 | SO-NEW
(1 row)

序列从 1 开始，而 1001~1003 已被占用。继续插入迟早撞键。

第五步：校正序列

sql
SELECT setval(
         pg_get_serial_sequence('orders', 'id'),
         (SELECT max(id) FROM orders)
       ) AS new_last_value;
text
 new_last_value
----------------
           1003
(1 row)
sql
INSERT INTO orders (order_no, amount) VALUES ('SO-AFTER-FIX', 88.00) RETURNING id, order_no;
text
  id  |  order_no
------+-------------
 1004 | SO-AFTER-FIX
(1 row)

第六步：迁移到 IDENTITY（复用原序列）

sql
ALTER TABLE orders ALTER COLUMN id DROP DEFAULT;

ALTER TABLE orders
  ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY
  ( SEQUENCE NAME orders_id_seq );
text
ALTER TABLE
ALTER TABLE
sql
-- 验证：序列名未变、值未回退
SELECT pg_sequences.last_value, pg_sequences.is_called
FROM pg_sequences WHERE sequencename = 'orders_id_seq';

INSERT INTO orders (order_no, amount) VALUES ('SO-IDENTITY', 1.00) RETURNING id;
text
 last_value | is_called
------------+-----------
       1004 | t
(1 row)

  id
------
 1005
(1 row)

第七步：扩容为 bigint 以防溢出

sql
ALTER SEQUENCE orders_id_seq AS bigint;
ALTER TABLE orders ALTER COLUMN id TYPE bigint;
text
ALTER SEQUENCE
ALTER TABLE
sql
-- 确认列与序列都是 bigint
SELECT
  'column'::text AS object, format_type(atttypid, atttypmod) AS type_name
FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attname = 'id'
UNION ALL
SELECT 'sequence', data_type FROM pg_sequences WHERE sequencename = 'orders_id_seq';
text
  object  | type_name
----------+-----------
 column   | bigint
 sequence | bigint
(2 rows)

第八步：巡检——找出所有孤儿序列

sql
SELECT c.relname AS orphan_sequence, pg_size_pretty(pg_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind = 'S'
  AND NOT EXISTS (
    SELECT 1 FROM pg_depend d
    WHERE d.objid = c.oid AND d.deptype IN ('a', 'i')
  )
  AND c.relnamespace = 'public'::regnamespace
ORDER BY c.relname;
text
   orphan_sequence
-----------------------
(0 rows)

第九步：巡检——找出接近上限的序列

sql
SELECT
  schemaname,
  sequencename,
  last_value,
  max_value,
  round(100.0 * last_value / nullif(max_value, 0), 4) AS used_pct
FROM pg_sequences
WHERE last_value IS NOT NULL
  AND max_value > 0
  AND data_type = 'integer'
ORDER BY used_pct DESC NULLS LAST
LIMIT 5;
text
 schemaname |  sequencename   | last_value |      max_value      | used_pct
------------+-----------------+------------+---------------------+----------
 public     | fix_demo_id_seq |        200 |          2147483647 |   0.0000
(1 row)

第十步：清理

sql
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS fix_demo;
DROP TABLE IF EXISTS migrate_demo;
DROP TABLE IF EXISTS gen_demo;
DROP TABLE IF EXISTS no_seq_demo;
DROP TABLE IF EXISTS t_drop_col;
DROP SEQUENCE IF EXISTS t_drop_col_id_seq;
text
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP SEQUENCE

注意事项 ​

  • SERIAL 不是数据类型。查 format_type() 会看到 integer，pg_type 里也搜不到 serial。别在类型转换或 \dT 里找它。

    sql
    SELECT format_type(atttypid, atttypmod) FROM pg_attribute
WHERE attrelid = 'demo_serial'::regclass AND attname = 'id';
    text
     format_type
-------------
 integer
(1 row)

  • SERIAL 的列类型被硬编码。SERIAL 永远是 integer（上限 21 亿），要 bigint 必须用 BIGSERIAL 或 IDENTITY。

  • 删列不会连带删掉 SERIAL 的序列，会留下孤儿序列。删列后记得手工 DROP SEQUENCE，或用 IDENTITY 规避。

    sql
    -- 不推荐：删列不清序列
ALTER TABLE t DROP COLUMN id;

  • SERIAL 需要额外授权。应用账号除了表的 INSERT，还要序列的 USAGE，否则报 permission denied for sequence。

    sql
    GRANT USAGE ON SEQUENCE public.demo_serial_id_seq TO app_role;

  • 手工插入 id 不会推进序列，必然导致后续自增撞主键。导入数据后必须用 pg_get_serial_sequence() + setval() 校正。

    sql
    SELECT setval(pg_get_serial_sequence('orders','id'), (SELECT max(id) FROM orders));

  • ALTER SEQUENCE ... AS bigint 需要 PostgreSQL 10+。低版本只能新建 bigint 序列再切换默认值。

  • 换列类型会重写表并重建索引，大表务必评估停机窗口。

  • GENERATED ALWAYS AS IDENTITY 下手工插入必须加 OVERRIDING SYSTEM VALUE，否则报 cannot insert a non-DEFAULT value。

    sql
    -- 错误！
INSERT INTO t (id, v) VALUES (100, 'x');
-- 正确
INSERT INTO t (id, v) OVERRIDING SYSTEM VALUE VALUES (100, 'x');

  • 生成列（GENERATED ALWAYS AS (expr) STORED）与身份列（AS IDENTITY）是两个不同特性，别被共用的 GENERATED 关键字误导。前者不能跨行、不能调用 nextval()。

  • 不要用 DEFAULT (SELECT max(id)+1 FROM t) 模拟自增，并发必冲突，删除后还会复用已删 id。

  • serial → identity 迁移建议用 BY DEFAULT 而非 ALWAYS，否则跨环境导入数据全部报错。

最佳实践 ​

  • 新项目一律用 bigint GENERATED ALWAYS AS IDENTITY，同时拿到「标准语法 + 强所有权 + 足够容量」：

    sql
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

  • 需要导入或保留历史 id 时用 BY DEFAULT，让 OVERRIDING 不再是必须：

    sql
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

  • 存量 SERIAL 表优先做 identity 迁移，复用原序列即可，不必重建数据：

    sql
    ALTER TABLE t ALTER COLUMN id DROP DEFAULT;
ALTER TABLE t ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (SEQUENCE NAME t_id_seq);

  • 数据导入脚本末尾固定跟一句 setval，把它写成导入流程的标准步骤而非事后补救：

    sql
    SELECT setval(pg_get_serial_sequence('orders','id'), (SELECT max(id) FROM orders));

  • 序列名统一约定 表名_列名_seq，便于批量运维、识别孤儿序列与自动化巡检。

  • 把孤儿序列巡检和容量巡检纳入常规运维：

    sql
    -- 孤儿序列
SELECT c.relname FROM pg_class c
WHERE c.relkind = 'S'
  AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.objid = c.oid AND d.deptype IN ('a','i'));

  • 容量巡检优先看 integer 序列，它们最容易触顶：

    sql
    SELECT sequencename, last_value, max_value,
       round(100.0 * last_value / nullif(max_value, 0), 4) AS used_pct
FROM pg_sequences
WHERE data_type = 'integer' AND last_value IS NOT NULL
ORDER BY used_pct DESC NULLS LAST LIMIT 10;

  • 显式写出 CACHE 与 NO CYCLE，即使取默认值，也让意图在 DDL 里可见：

    sql
    CREATE TABLE t (
  id bigint GENERATED ALWAYS AS IDENTITY (CACHE 1 NO CYCLE) PRIMARY KEY
);

  • 不要依赖自增值连续。回滚、缓存预取、崩溃、并发冲突都留洞，需要「连续编号」时用 row_number() 现算，不要用自增列。

  • 迁移前先在副本上验证 pg_dump 输出，确认序列、OWNED BY、SET DEFAULT 三件套完整还原。

下一节

继续学习：UUID 生成函数

参考链接 ​