Spring JPA/HIbernate: duplicate key value violates unique constraint

Question

Searching amongst existing answers gave no valid results. So here is the error I can't find the reason for. I have an entity class MeteoRecord and DayDegreedefined as follows:

@Getter
@Setter
@Entity
public class MeteoRecord extends AbstractEntity {

    @Temporal(TemporalType.DATE)
    Date date;
...
//other attributes
}

@Entity
@Getter
@Setter
public class DayDegree extends AbstractEntity {

    @ManyToOne
    @JoinColumn(name = "meteo_record_id")
    private MeteoRecord meteoRecord;
...
//other attributes
}

All the entities extend AbstractEntity class:

@MappedSuperclass
@Getter
@Setter
public abstract class AbstractEntity implements Serializable {

    @Access(AccessType.PROPERTY)
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @CreationTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "DATCRE")
    public Date datcre;

    @UpdateTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "DATMOD")
    public Date datmod;
}

When saving a new MeteoRecord as follows:

Optional<MeteoRecord> meteoByDate = meteoRecordRepository.findByDate(dateAsDate);
  MeteoRecord meteoRecord;
  if (meteoByDate.isPresent()) {
      meteoRecord = meteoByDate.get();
  } else {
      meteoRecord = new MeteoRecord();
      meteoRecord.setDate(dateAsDate);
  }
MeteoRecord savedMeteoRecord = meteoRecordRepository.save(meteoRecord);
...

it raises the error:

rg.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "meteo_record_pkey"
  Detail: Key (id)=(1680) already exists.

The `MeteoRepositiry just extends `` JPARepository:

@Repository
public interface MeteoRecordRepository extends JpaRepository<MeteoRecord, Long> {
...
Optional<MeteoRecord> findByDate(Date date);
}

I activated more logs to see the SQL queries but see no additional queries creating a new MeteoRecord nor inserting a DayDegree record elsewhere:

11:33:55.664 [http-nio-8080-exec-1] DEBUG org.hibernate.SQL - 
    select
        meteorecor0_.id as id1_33_,
        meteorecor0_.datcre as datcre2_33_,
        meteorecor0_.datmod as datmod3_33_,
        meteorecor0_.date as date4_33_,
        meteorecor0_.degree_day_15 as degree_d5_33_,
        meteorecor0_.degree_day_15_eq as degree_d6_33_,
        meteorecor0_.station as station7_33_,
        meteorecor0_.temp_avg as temp_avg8_33_,
        meteorecor0_.temp_avg_eq as temp_avg9_33_ 
    from
        meteo_record meteorecor0_ 
    where
        meteorecor0_.date=?
Hibernate: 
    select
        meteorecor0_.id as id1_33_,
        meteorecor0_.datcre as datcre2_33_,
        meteorecor0_.datmod as datmod3_33_,
        meteorecor0_.date as date4_33_,
        meteorecor0_.degree_day_15 as degree_d5_33_,
        meteorecor0_.degree_day_15_eq as degree_d6_33_,
        meteorecor0_.station as station7_33_,
        meteorecor0_.temp_avg as temp_avg8_33_,
        meteorecor0_.temp_avg_eq as temp_avg9_33_ 
    from
        meteo_record meteorecor0_ 
    where
        meteorecor0_.date=?
11:33:55.677 [http-nio-8080-exec-1] DEBUG o.s.d.r.c.s.TransactionalRepositoryProxyPostProcessor$CustomAnnotationTransactionAttributeSource - Adding transactional method 'save' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
11:33:55.679 [http-nio-8080-exec-1] DEBUG o.s.orm.jpa.JpaTransactionManager - Found thread-bound EntityManager [SessionImpl(1454087429<open>)] for JPA transaction
11:33:55.680 [http-nio-8080-exec-1] DEBUG o.s.orm.jpa.JpaTransactionManager - Creating new transaction with name [org.springframework.data.jpa.repository.support.SimpleJpaRepository.save]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
11:33:55.680 [http-nio-8080-exec-1] DEBUG o.h.e.t.internal.TransactionImpl - On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
11:33:55.680 [http-nio-8080-exec-1] DEBUG o.h.e.t.internal.TransactionImpl - begin
11:33:55.681 [http-nio-8080-exec-1] DEBUG o.s.orm.jpa.JpaTransactionManager - Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@27864a10]
11:33:55.696 [http-nio-8080-exec-1] DEBUG org.hibernate.engine.spi.ActionQueue - Executing identity-insert immediately
11:33:55.703 [http-nio-8080-exec-1] DEBUG org.hibernate.SQL - 
    insert 
    into
        meteo_record
        (datcre, datmod, date, degree_day_15, degree_day_15_eq, station, temp_avg, temp_avg_eq) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: 
    insert 
    into
        meteo_record
        (datcre, datmod, date, degree_day_15, degree_day_15_eq, station, temp_avg, temp_avg_eq) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?)
11:33:55.732 [http-nio-8080-exec-1] DEBUG o.h.e.jdbc.spi.SqlExceptionHelper - could not execute statement [n/a]
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "meteo_record_pkey"
  Detail: Key (id)=(1681) already exists.

What am I missing? I have an impression that it is because of a relation defined in DayDegree entity but see no queries nor use of DayDegree entity.

Thank you.

Do you run your problematic code snippet inside one transaction? — SternK
– SternK, Commented May 12, 2021 at 8:43
The service class where the code is run is annotated with @Transactional. — belgoros
– belgoros, Commented May 12, 2021 at 8:44
But use or not the @Transaction annotation does not influence the error at all, it happens both with or without it. — belgoros
– belgoros, Commented May 12, 2021 at 9:35
looks weird that there is no id field in the sql generated by hibernate.. how do you define it in the MeteoRecord and how does it managed in the DB? — Yuriy Tsarkov
– Yuriy Tsarkov, Commented May 12, 2021 at 9:41
since there is an Executing identity-insert immediately in the log I would guess that you are using GenerationType.IDENTITY, where as SEQUENCE is much preferable in PostgreSQL. Any way, are you shure that the DB is consistent and there are no other records with a given id? — Yuriy Tsarkov
– Yuriy Tsarkov, Commented May 12, 2021 at 9:55

Yuriy Tsarkov · Accepted Answer · 2021-06-01 13:58:11Z

1

According to this:

When I check the table meteo_record, sure, there is always a record with the id indicated in the error: select * from meteo_record mr where mr.id = 1678;

I can assume that the issue is in the DB. Hibernate doesn't look for a record by id creating a new one, but it just requires the next identity from the database. Since it IDENTITY it'll get a next value of the strategy (+1.. or whatever). Now lets imagine you've defined incrementation strategy as INCREMENT BY 1 and at the moment the current value is 12. So it's clear that the next value will be 13 during the entity persistence. But somehow (e.g. by the direct DB sql insertion with id column) record with id=13 has been inserted. In this case Hibernate know nothing about that coz identity has not been incremented. Here we are. Now any attempt of inserting using identity will lead us to the given exception.

I think you should do soething like this:

select max(id) + 1 from meteo_record;

and use resulting value (x) in this:

ALTER TABLE meteo_record ALTER COLUMN id RESTART WITH x;

UPD

Here is a DDL for the meteo_record

CREATE TABLE public.meteo_record (     
    id bigint NOT NULL DEFAULT nextval('meteo_record_id_seq'::regclass),     
    datcre timestamp without time zone,     
    datmod timestamp without time zone,     
    date date,     
    degree_day_15 double precision,     
    degree_day_15_eq double precision,     
    station character varying(255) COLLATE pg_catalog."default",     
    temp_avg double precision,     
    temp_avg_eq double precision,     
    CONSTRAINT meteo_record_pkey PRIMARY KEY (id) 
)

since here we have a sequence, the correct command to reset it woukd be

ALTER SEQUENCE meteo_record_id_seq RESTART WITH 1973;

edited Jun 1, 2021 at 13:58

answered May 12, 2021 at 10:53

Yuriy Tsarkov

2,5882 gold badges18 silver badges29 bronze badges

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

7 Comments

belgoros Over a year ago

Thank you for the response and advice on how to reset the IDs in the DB. It seems like it is a data-related issue because once I dropped all the records from meteo_record and day_degree tables and used the latest DB dump backup, everything came back to normal

belgoros Over a year ago

Coming back to the same issue, I tried to execute select max(id) + 1 from meteo_record; which gave me 1973. Now when running ALTER TABLE meteo_record ALTER COLUMN id RESTART WITH 1973; it fails with ERROR: column "id" of relation "meteo_record" is not an identity column, followed by SQL state: 55000. What could it be the reason ?

Yuriy Tsarkov Over a year ago

what DDL generates pgAdmin (or whatever editor) for the meteo_record?

belgoros Over a year ago

Hmm, how can I find it if I use pgAdmin?

Yuriy Tsarkov Over a year ago

@belgoros yep, it is. I've updated the answer

|

Collectives™ on Stack Overflow

Spring JPA/HIbernate: duplicate key value violates unique constraint

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related