Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "

时间:2021-10-04 02:12:14

插入数据postgre 报错如下

01 ### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "vehicle__appKey_pkey"
02 ### The error may involve com.sq.platform.basePlatform.dao.VehicleMapper.insertVehicleAppkey-Inline
03 ### The error occurred while setting parameters
04 ### SQL: insert into vehicle__appkey(vehicle_id_fk, appkey_id_fk) values                      (?, ?)
05 ### Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "vehicle__appKey_pkey"
06 ; SQL []; ERROR: duplicate key value violates unique constraint "vehicle__appKey_pkey"; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "vehicle__appKey_pkey"
07         at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
08         at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
09         at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
10         at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399)
11         at com.sun.proxy.$Proxy11.insert(Unknown Source)

表中 vehicle__appkey  id字段如下

1 id integer NOT NULL DEFAULT nextval('"vehicle__appKey_id_seq"'::regclass),
1  
1  
1 vehicle__appKey_pkey 序列表如下
1 <span style="font-family: Arial, Helvetica, sans-serif;">-- Sequence: "vehicle__appKey_id_seq"</span>
01 <pre name="code" class="java">
02 -- DROP SEQUENCE "vehicle__appKey_id_seq";
03  
04 CREATE SEQUENCE "vehicle__appKey_id_seq"
05   INCREMENT 1
06   MINVALUE 1
07   MAXVALUE 9223372036854775807
08   <span style="color:#ff0000;">START 711</span>
09   CACHE 1;
10 ALTER TABLE "vehicle__appKey_id_seq"
11   OWNER TO postgres;


不难看出以上错误是由于 vehicle__appkey中id字段重复导致的,而id字段的值来自于 vehicle__appKey_pkey 序列表,   START 711 标示下一个id值为712,而712的id   vehicle__appkey表中已经存在了,所以插入数据时报以上这个错误

解决方法:将"vehicle__appKey_id_seq"中的当前值设为为vehicle__appkey  id的最大值