java - Spring Data PostgreSQL Key already exists 错误2

Question

在我的 Spring Boot/Data/JPA 应用程序中，我有一个以下 PostgreSQL 表:

CREATE TABLE IF NOT EXISTS levels (
    id SERIAL PRIMARY KEY,
    name VARCHAR(256),
    created_date timestamp,
    updated_date timestamp
);

和 Spring Data/JPA 实体:

@Entity
@Table(name = "levels")
public class Level extends BaseEntity implements Serializable {

    private static final long serialVersionUID = 642499791438799548L;

    @Id
    @SequenceGenerator(name = "levels_id_seq", sequenceName = "levels_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "levels_id_seq")
    private Long id;

    private String name;

...
}

此外，我在数据库中添加了 10 个级别:

    INSERT INTO levels (id, name) VALUES (1, 'Level 1') ON CONFLICT (id) DO NOTHING;
    INSERT INTO levels (id, name) VALUES (2, 'Level 2') ON CONFLICT (id) DO NOTHING;
    INSERT INTO levels (id, name) VALUES (3, 'Level 3') ON CONFLICT (id) DO NOTHING;
    INSERT INTO levels (id, name) VALUES (4, 'Level 4') ON CONFLICT (id) DO NOTHING;
    INSERT INTO levels (id, name) VALUES (5, 'Level 5') ON CONFLICT (id) DO NOTHING;
    INSERT INTO levels (id, name) VALUES (6, 'Level 6') ON CONFLICT (id) DO NOTHING;
    INSERT INTO levels (id, name) VALUES (7, 'Level 7') ON CONFLICT (id) DO NOTHING;
    INSERT INTO levels (id, name) VALUES (8, 'Level 8') ON CONFLICT (id) DO NOTHING;
    INSERT INTO levels (id, name) VALUES (9, 'Level 9') ON CONFLICT (id) DO NOTHING;
    INSERT INTO levels (id, name) VALUES (10, 'Level 10') ON CONFLICT (id) DO NOTHING;

当我尝试创建一个新关卡时:

@Repository
public interface LevelRepository extends JpaRepository<Level, Long> {

    Level findByName(String name);

}

level = new Level();
level.setName(name);
level = levelRepository.save(level);

我收到以下异常:

ERROR:  duplicate key violates unique constraint « levels_pkey »
DETAIL: Key "(id)=(1)" already exists.

为什么系统选择 id=1 而不是例如 11 ？

Answer 1

序列生成器从 1 开始，并在每次通过 JPA 实现框架插入或在插入语句中调用生成器后递增。如果您使用 RDBMS 序列生成器，则不应在原始 SQL 中插入硬编码 ID 值。

现在你可以修复序列了:

ALTER SEQUENCE levels_id_seq
  INCREMENT 1
  MINVALUE 11
  MAXVALUE 9223372036854775807
START 11
CACHE 1;
ALTER TABLE levels
  OWNER TO postgres;

或者像这样修复插入:

INSERT INTO levels (id, name) VALUES (nextval(levels_id_seq), 'Level 1') ON CONFLICT (id) DO NOTHING;