PostgreSQL 不使用 AUTO_INCREMENT 关键字（那是 MySQL 的写法），而是使用 序列（SEQUENCE） + SERIAL / IDENTITY。
方式
说明
SERIAL
老版本兼容（内部创建序列）
IDENTITY
SQL 标准，推荐（PostgreSQL 10+）
SEQUENCE
底层对象，可独立管理
二、三种实现方式对比
方式
语法
是否标准
是否可控
推荐
SERIAL
id SERIAL
否
一般
兼容旧项目
BIGSERIAL
id BIGSERIAL
否
一般
大数据量
IDENTITY
id GENERATED ALWAYS AS IDENTITY
是
高
强烈推荐
三、基本使用示例
1. SERIAL（传统方式）
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT
);
-- 插入时自动增长
INSERT INTO users(name) VALUES ('Alice');
-- id = 1
INSERT INTO users(name) VALUES ('Bob');
-- id = 2
内部自动创建：users_id_seq
2. IDENTITY（推荐方式）
CREATE TABLE products (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT,
price NUMERIC
);
-- 插入
INSERT INTO products(name, price) VALUES ('iPhone', 999);
-- id = 1
两种 IDENTITY 模式
模式
说明
GENERATED ALWAYS
默认，禁止手动插入
GENERATED BY DEFAULT
允许手动插入（若不填则自动）
-- 允许手动指定 ID
CREATE TABLE logs (
id BIGINT GENERATED BY DEFAULT AS IDENTITY,
message TEXT
);
INSERT INTO logs(id, message) VALUES (100, 'Custom ID');
-- 重新编号（慎用！影响外键）
WITH ranked AS (
SELECT id, row_number() OVER (ORDER BY id) AS rn
FROM users
)
UPDATE users u
SET id = r.rn
FROM ranked r
WHERE u.id = r.id;
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id INT REFERENCES users(id)
);
-- 没问题
2. 逻辑复制 / pg_dump
IDENTITY 列会自动处理
SERIAL 需注意序列权限
-- 导出时包含序列
pg_dump -Fc -f backup.dump dbname
3. MySQL 迁移到 PostgreSQL
-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY
-- 转为 PostgreSQL
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
九、查看与管理序列
-- 查看所有序列
SELECT * FROM information_schema.sequences;
-- 查看系统序列
SELECT schemaname, sequencename, last_value
FROM pg_sequences;
-- 修改序列
ALTER SEQUENCE users_id_seq
INCREMENT BY 2
MINVALUE 1
MAXVALUE 1000000
RESTART WITH 1;
十、常见错误与避坑
错误
原因
解决
ERROR: duplicate key value violates unique constraint
手动插入冲突
用 BY DEFAULT 或 OVERRIDING SYSTEM VALUE
sequence is not yet defined in this session
currval() 未调用 nextval()
先 nextval()
ID 跳跃
事务回滚、CACHE
正常现象
迁移后 ID 从 1 开始
未 RESTART IDENTITY
TRUNCATE ... RESTART IDENTITY
GENERATED ALWAYS 插入失败
显式插入
省略列或用 BY DEFAULT
插入时覆盖 IDENTITY（特殊场景）
INSERT INTO products(id, name)
VALUES (999, 'Legacy Product')
OVERRIDING SYSTEM VALUE;
十一、最佳实践脚本
1. 标准建表模板（推荐）
CREATE TABLE customers (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name TEXT,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
-- 触发器自动更新 updated_at
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_customers_updated_at
BEFORE UPDATE ON customers
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();
2. 安全插入（支持覆盖）
-- 允许导入旧数据
INSERT INTO customers(id, email, name)
VALUES (1001, 'old@example.com', 'Legacy User')
OVERRIDING SYSTEM VALUE;
-- 插入并返回 ID（推荐）
INSERT INTO users(name) VALUES ('Tom')
RETURNING id;
十三、速查表
命令
用途
SERIAL
自动创建序列
GENERATED ALWAYS AS IDENTITY
标准，禁止手动插入
GENERATED BY DEFAULT AS IDENTITY
允许手动插入
nextval('seq')
获取下一个值
setval('seq', n)
设置当前值
TRUNCATE ... RESTART IDENTITY
重置序列
OVERRIDING SYSTEM VALUE
插入时覆盖 ID
RETURNING id
获取插入的 ID
十四、决策树
graph TD
A[需要自动增长 ID?] -->|是| B{是否 PostgreSQL 10+?}
B -->|是| C[用 GENERATED ALWAYS AS IDENTITY]
B -->|否| D[用 SERIAL]
C --> E{是否需要手动插入?}
E -->|是| F[用 BY DEFAULT]
E -->|否| G[用 ALWAYS]