一、为什么需要从SERIAL迁移到IDENTITY
在PostgreSQL的早期版本中，我们习惯使用SERIAL类型来创建自增列。它本质上是一个语法糖，底层通过序列（sequence）和默认值来实现自增功能。但随着PostgreSQL 10的发布，引入了标准SQL定义的IDENTITY列，这成为了更现代、更规范的自增列实现方式。
举个例子，我们来看一个典型的SERIAL用法：
-- 使用SERIAL创建自增列（PostgreSQL传统方式）
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- SERIAL会自动创建序列并绑定
name VARCHAR(100)
);
虽然SERIAL用起来简单，但它有几个潜在问题：
- 不够直观：
SERIAL是PostgreSQL特有的语法，不是SQL标准的一部分，对于从其他数据库迁移过来的开发者可能不太友好。 - 隐藏细节：它隐式创建序列，如果开发者在不知情的情况下直接操作序列，可能导致意外行为。
- 权限问题：自动创建的序列可能需要额外权限管理。
而IDENTITY列则更符合SQL标准，行为更明确：
-- 使用IDENTITY创建自增列（PostgreSQL 10+推荐方式）
CREATE TABLE products (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- 标准语法，行为更清晰
product_name VARCHAR(100)
);
二、IDENTITY列的核心特性
IDENTITY列有两种生成策略：
GENERATED ALWAYS AS IDENTITY：强制使用序列，手动插入值会报错（除非用OVERRIDING SYSTEM VALUE）。GENERATED BY DEFAULT AS IDENTITY：允许手动插入值，仅在未指定值时使用序列。
来看一个更完整的例子：
-- 创建带有IDENTITY列的表
CREATE TABLE orders (
order_id INT GENERATED ALWAYS AS IDENTITY, -- 必须由系统生成
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
amount DECIMAL(10,2),
PRIMARY KEY (order_id)
);
-- 尝试手动插入值会报错
INSERT INTO orders (order_id, amount) VALUES (100, 99.99); -- 错误！
-- 必须用OVERRIDING SYSTEM VALUE才能插入
INSERT INTO orders (order_id, amount)
OVERRIDING SYSTEM VALUE VALUES (100, 99.99); -- 显式覆盖系统生成
-- 正常插入（由系统生成ID）
INSERT INTO orders (amount) VALUES (200.00); -- 成功，自动分配ID
相比之下，GENERATED BY DEFAULT更宽松：
CREATE TABLE inventory (
item_id INT GENERATED BY DEFAULT AS IDENTITY, -- 允许手动指定
item_name VARCHAR(100)
);
-- 两种插入方式都合法
INSERT INTO inventory (item_id, item_name) VALUES (10, 'Laptop'); -- 手动指定
INSERT INTO inventory (item_name) VALUES ('Phone'); -- 自动生成
三、从SERIAL迁移到IDENTITY的实践步骤
假设我们有一个现有的使用SERIAL的表，如何平滑迁移到IDENTITY？以下是具体步骤：
步骤1：检查现有表的SERIAL列
-- 查看当前表的定义
\d old_serial_table
-- 确认序列名称（SERIAL隐式创建的序列通常是"表名_列名_seq"）
SELECT sequence_name FROM information_schema.sequences;
步骤2：创建新表（使用IDENTITY）
-- 创建结构相同但使用IDENTITY的新表
CREATE TABLE new_identity_table (
id INT GENERATED BY DEFAULT AS IDENTITY, -- 注意选择ALWAYS或BY DEFAULT
data VARCHAR(100)
);
-- 或者直接修改现有表（PostgreSQL 10+）
ALTER TABLE old_serial_table
ALTER COLUMN id SET DATA TYPE INT, -- 确保类型兼容
ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY; -- 转换为IDENTITY
步骤3：迁移数据
-- 复制数据（假设我们选择创建新表）
INSERT INTO new_identity_table (id, data)
SELECT id, data FROM old_serial_table;
-- 如果序列需要保留原值，重置序列
ALTER SEQUENCE new_identity_table_id_seq
RESTART WITH (SELECT MAX(id)+1 FROM new_identity_table);
步骤4：测试验证
-- 验证自动递增功能
INSERT INTO new_identity_table (data) VALUES ('Test1');
INSERT INTO new_identity_table (data) VALUES ('Test2');
-- 检查最新ID是否连续
SELECT * FROM new_identity_table ORDER BY id DESC LIMIT 5;
四、技术对比与注意事项
4.1 性能差异
在实际测试中，IDENTITY和SERIAL的性能几乎相同，因为底层都使用序列。但IDENTITY有这些优势：
- 更清晰的意图表达
- 更好的与其他数据库兼容
- 更细粒度的控制（通过
OVERRIDING SYSTEM VALUE）
4.2 注意事项
- 版本兼容性：
IDENTITY需要PostgreSQL 10+，旧版本只能使用SERIAL。 - 迁移时机：建议在低流量时段执行迁移，避免锁表问题。
- 外键约束：如果其他表引用了自增列，需要同步更新外键关系。
- 应用代码：检查应用代码是否依赖
SERIAL的特定行为，如直接操作序列。
4.3 应用场景推荐
- 新项目：直接使用
IDENTITY，无需考虑SERIAL。 - 老项目迁移：如果表结构简单，可以原地修改；复杂系统建议逐步迁移。
- 跨数据库兼容：如果计划支持多种数据库，
IDENTITY是更好选择。
五、总结
从SERIAL到IDENTITY的迁移代表了PostgreSQL向SQL标准靠拢的趋势。虽然改动看似微小，但它带来了更规范的语法、更明确的行为和更好的兼容性。对于新项目，毫无疑问应该选择IDENTITY；对于现有系统，可以根据实际情况制定渐进式迁移计划。
记住关键点：
GENERATED ALWAYS更严格，适合需要强制自增的场景GENERATED BY DEFAULT更灵活，兼容现有数据- 迁移时注意序列值的连续性
通过合理规划，这次技术升级可以做到业务无感知，为未来的数据库维护打下更好基础。
评论