Spring Data PostgreSQL Key already exists error 2

Question

In my Spring Boot/Data/JPA application I have a following PostgreSQL table:

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

and Spring Data/JPA entity:

@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;

...
}

Also, I have added 10 Levels into database:

    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;

When I try to create a new level:

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

    Level findByName(String name);

}

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

I'm receiving a following exception:

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

Why the system chooses id=1 and not for example 11 ?

Cootri · Accepted Answer · 2016-03-19 09:45:20Z

4

sequence generator starts from 1 and increments after each insert via JPA implementation framework or using call to generator in insert statement. You shouldn't insert hardcoded ID values in raw SQL if you are using RDBMS sequence generator.

Now you can fix sequence:

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

Or fix inserts like:

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

edited Mar 19, 2016 at 9:45

answered Mar 19, 2016 at 9:34

Cootri

3,84623 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spring Data PostgreSQL Key already exists error 2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related