Postgres数据库，报错Key (id)=(1849) already exists，数据库中主键已经存在

只想知道何如解决的小伙伴，可以直接跳到第三章节哦！

1、问题原因   

        在Postgres数据库，执行insert语句时，数据库报错Key (id)=(1849) already exists。数据库id为1849的记录已经存在。

      一般出现这个情况的原因为，表的主键是自增的，手动插入数据或通过sql插入但给主键赋值时，无法触发自增序列。当不指定主键，使用自增序列插入数据时，就可能会报主键已经存在的错误。

2、自增序列原理

      原理：pg数据库通过自增序列实现主键自增，每次新增数据时，如果未指定主键，会查询当前自增序列的值，并将值加1，当作新的主键值。如果数据库已经存在一条id与当前自增序列值加1相等的值，就会报主键值已经存在。

  情景模拟：

（1）创建student表

create table student(
	id int primary key,
	name varchar(10) 
);

（2）创建自增序列，并设置其实值为1，步长为1。将序列与student表主键关联，实现自增。

//创建序列
CREATE SEQUENCE table_student_id_seq;

//将序列与student表的主键关联
ALTER TABLE student ALTER COLUMN id SET DEFAULT nextval('table_student_id_seq');

//设置初始值为1
ALTER SEQUENCE table_student_id_seq START WITH 1;
 
//设置步长为1
ALTER SEQUENCE table_student_id_seq INCREMENT BY 1;

（3）通过sql添加一条数据，不指定id，即通过自增序列指定主键。

insert into student(name) values('张三');

查询当前student表，新的数据的id为1

select * from student;

id|name|
--+----+
 1|张三  |

 

查询序列 table_student_id_seq当前值，当前序列值为1

SELECT currval('table_student_id_seq');

currval|
-------+
      1|

（4）手动插入一条数据，指定id=3，不触发自增序列。

 insert into student(id,name) values(3,'张三');
 select * from student;

id|name|
--+----+
 1|张三  |
 3|张三  |

数据库里存在一条id为1，3的数据

（5）通过sql添加一条数据，不指定id，即通过自增序列指定主键。由于上次自增序列为1，新插入的数据的id应该为1+1=2,可以正常插入。

不指定id，新插入一条数据

insert into student(name) values('张三');

当前student表内容：

id|name|
--+----+
 1|张三  |
 3|张三  |
 2|张三  |

查询当前序列值：

SELECT currval('table_student_id_seq');

currval|
-------+
      2|

（6）通过sql添加一条数据，不指定id，即通过自增序列指定主键。上次自增后，自增序列值为2，新插入的数据id应该为2+1=3,但是数据库已经存了id为3的数据。因此就会报错Key (id)=(3) already exists。

不指定id，新插入一条数据

insert into student(name) values('张三');

报错：

SQL 错误 [23505]: ERROR: duplicate key value violates unique constraint "student_pkey"
  Detail: Key (id)=(3) already exists.

3、解决方案

重新设置序列值，使序列值为当前数据库id的最大值即可。

• 查询当前表主键的最大值：select max(主键) from 表名。
• 设置设置序列值：select setval(序列名, 新值);

综合上面两个方法，可以合成为一个sql:

select setval(序列名, select max(主键) from 表名);

通过上一节情景演示解决方案：

（1）重新设置序列table_student_id_seq为student表id的最大值.

//设置序列为主键最大值

select setval('table_student_id_seq', (select max(id) from student));

查询当前序列值：
SELECT currval('table_student_id_seq');

currval|
-------+
      3|

（2）通过sql添加一条数据，不指定id，即通过自增序列指定主键。由于重新设置序列值，当前序列值为3，新增记录的序列值为3+1=4。数据库没有id为4的记录，插入成果。

insert into student(name) values('张三');

查询数据库记录
 select * from student;

id|name|
--+----+
 1|张三  |
 3|张三  |
 2|张三  |
 4|张三  |