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.

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