postgresql自增序列和自增重设

postgresql的自增id

postgresql的自增id通过serial类型进行定义

类型	大小/bytes	范围
smallserial	2	2^15-1
serial	4	2^31-1
bigserial	8	2^63-1

##创建自增id为主键的tb_aa表
CREATE TABLE tb_aa (
	id serial NOT NULL,
	user_id int4 NOT NULL,
	status varchar(5) NOT NULL DEFAULT '1'::character varying,
	update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT tb_aa_pk PRIMARY KEY (id),
	CONSTRAINT tb_aa_user_id_key UNIQUE (user_id)
);

##创建自增id实际执行：
CREATE TABLE tb_aa (
id integer not null default('tb_aa_id_seq'),
...
);

alter sequence tb_aa_id_seq owned by tb_aa.id;

##获取serial序列名称
select pg_get_serial_sequence('tb_aa','id');

##获取序列的最大id值
select setval('tb_aa_id_seq', max(id)) from tb_aa;

serial重设

问题：在批量插入数据后，手动删除后，重新批量插入导致出现主键冲突的错误
SQL 错误 [23505]: ERROR: duplicate key value violates unique constraint “tb_aa_pk”
Detail: Key (id)=(11) already exists.

可能原因：
1.同时写入导致的冲突
2.自增id出现断层在批量插入数据的时候，显示主键冲突

##重新设置起始序列
alter sequence tb_aa_id_seq restart with 4;

##查看下一个序列号
select nextval('tb_aa_id_seq');

清空表后的自增id重设

##插入数据测试
insert into tb_aa(user_id) values(1),(2),(3),(4),(5),(6),(7);
select * from tb_aa;
truncate table tb_aa;

##自增id从8开始
insert into tb_aa(user_id) values(1),(2),(3),(4),(5),(6),(7);

##清空并重设
truncate table tb_aa cascade; 
truncate tb_aa restart identity cascade;

##自增id从1开始
insert into tb_aa(user_id) values(1),(2),(3),(4),(5),(6),(7);