Spring Data PostgreSQL Key already exists error

Question

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 corresponding Spring Data 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.SEQUENCE, generator = "levels_id_seq")
    private Integer id;

    private String name;

...
}

@MappedSuperclass
public abstract class BaseEntity {

    @Column(name = "created_date")
    private Date createdDate;

    @Column(name = "updated_date")
    private Date updatedDate;

    @PrePersist
    void onCreate() {
        setCreatedDate(new Date());
    }

    @PreUpdate
    void onPersist() {
        setUpdatedDate(new Date());
    }

...
}

this is my Spring Data Level Repository:

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

    Level findByName(String name);

}

Right now when I try to save a new Level entity with Spring LevelRepository I receive a following error:

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

What can be wrong with my configuration ?

Are you sure you don't have an entry in your db, already with an ID=5? — Gergely Bacso
– Gergely Bacso, Commented Mar 8, 2016 at 15:58
I already have entity with ID=5 in my db.. but I think it should be smart enough in order to generate new non existent ID... but anyway I can be wrong because I'm new with PostgreSQL — alexanoid
– alexanoid, Commented Mar 8, 2016 at 16:02

airush · Accepted Answer · 2019-07-18 18:22:08Z

4

If you want to leave your GenerationType instead of using GenarationType.AUTO from that answer

and the error occurs probably cause of hardcoded inserts in data.sql file.

Insert using defined sequence in the entity

INSERT INTO levels(id, name) VALUES (nextval('levels_id_seq'), 'test');

the same way with GenerationType.IDENTITY it will create the sequence "%Your-table-name%_id_seq".

answered Jul 18, 2019 at 18:22

airush

7809 silver badges21 bronze badges

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

1 Comment

Younes El Ouarti Over a year ago

Thanks a bunch! I'm upgrading to Hibernate 5 and somehow tests were failing. All of the code we have looked correct and nowhere did I find a similar answer like yours!

gonzo · Accepted Answer · 2016-03-08 16:05:39Z

3

Use strategy=GenerationType.AUTO instead and you should be good. strategy=GenerationType.AUTO will use a table to generate the ID's instead of using a sequence.

answered Mar 8, 2016 at 16:05

gonzo

2,1312 gold badges18 silver badges28 bronze badges

Collectives™ on Stack Overflow

Spring Data PostgreSQL Key already exists error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related