PostgreSQL AUTO INCREMENT(自动增长) 的使用_MsSqlserver

下面是一份 postgresql 自动增长（auto increment）的完整实战手册，涵盖序列（sequence）、serial、identity、自定义、并发安全、性能、迁移、常见陷阱与最佳实践，适合开发、dba、架构师使用。

一、postgresql 的自动增长机制

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');

四、序列（sequence）底层原理

-- 查看自动创建的序列
select pg_get_serial_sequence('users', 'id');
-- 返回: public.users_id_seq

-- 直接操作序列
select nextval('users_id_seq');  -- 获取下一个值
select setval('users_id_seq', 100);  -- 设置当前值
select currval('users_id_seq');  -- 获取当前值（事务内）

手动创建序列

create sequence my_seq
    start with 1000
    increment by 1
    minvalue 1
    maxvalue 999999
    cache 10;

create table orders (
    id int primary key default nextval('my_seq'),
    total numeric
);

五、identity vs serial 详细对比

特性	serial	identity
sql 标准	否	是
可显式插入	允许	always 禁止，by default 允许
序列名固定	是（table_col_seq）	是（系统命名）
可重用序列	困难	容易
迁移兼容性	好	需 postgresql 10+
推荐度	3 stars	5 stars

六、常见操作：重置、跳跃、修复

1. 重置 id 从 1 开始

-- serial 表
truncate table users restart identity;

-- 或手动
select setval(pg_get_serial_sequence('users', 'id'), 1, false);

2. 修复 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;

3. 跳过一段 id（预留）

select setval('users_id_seq', 10000);
-- 下一个 insert 从 10001 开始

七、并发安全与性能

场景	行为
高并发插入	安全（序列是事务安全的）
事务回滚	id 不回退（序列已分配）
缓存（cache）	提升性能，但回滚会造成空洞

-- 创建高性能序列
create sequence fast_seq cache 100;

-- 100 个值预分配，减少锁竞争

空洞是正常现象，不要试图消除

八、与外键、复制、迁移

1. 外键引用

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;

3. 重置测试数据

truncate table customers, orders, products restart identity cascade;

十二、性能优化建议

建议	说明
用 bigint 而非 int	避免溢出
序列 cache 20~100	提升插入性能
分区表用独立序列	避免热点
避免 select currval()	用 returning id

-- 插入并返回 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

十四、决策树

十五、常见面试题

问题	答案
postgresql 如何实现自增？	使用 sequence
serial 和 identity 区别？	identity 是标准，serial 是扩展
事务回滚后 id 会回退吗？	不会
如何插入指定 id？	overriding system value
如何查看当前序列值？	select last_value from seq_name

十六、迁移对照表（mysql → postgresql）

mysql	postgresql
auto_increment	generated always as identity
int auto_increment primary key	int generated always as identity primary key
bigint auto_increment	bigint generated always as identity
insert ignore	on conflict do nothing

到此这篇关于postgresql auto increment(自动增长) 的使用的文章就介绍到这了,更多相关postgresql auto increment内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

PostgreSQL AUTO INCREMENT(自动增长) 的使用

2025年11月06日 • MsSqlserver •我要评论