PostgreSQL claims my query has violated unique key constraint on id

Question

I'm struggling with an error, and I think it has to do with PostgreSQL. I'm trying to insert values in a database created 3-4years ago. The java program use Hibernate and lombok.

The original program written 3-4years ago takes an entire list of everything in de db, gives an id value to new items, and them performs a saveAll() to the repository. This gave a PSQLException: ERROR: duplicate key value violates unique constraint "class_pkey" Detail: Key (id)=(17) already exists.

First, I split the list with only the new values so I was sure there wasn't a duplicate value, but the problem persists.

Second, I made sure the lists id values were set back to null so the @GeneratedValue could do its work. This also doesn't make the problem go away.

I found similar topic: Why does Postgres generate an already used PK value?. But I use Hibernate, so I cannot set the query. Also I already tried only inserting ids above the max in current db, this didn't change anything.

Other topics didn't help me resolving this problem, nor did they gave me an accurate view on what the problem is.

• How is this PSQLException thrown? (again, there are no conflicting unique constraints in reality)
• what can I do to solve this?

I'm showing the original code below. As said, I tried changing some thins as mentioned above. You can only see the original code here.

Entity:

@Entity
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class NationalHoliday extends AuditableEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String holidayName;
private LocalDate holidayDate;
private boolean fixedDate;



}

service (repository seems obvious to me)

@Service
@RequiredArgsConstructor
public class NationalHolidayServiceImpl implements NationalHolidayService {
private final NationalHolidayRepository nationalHolidayRepository;

@Override
public void updateNationalHolidays(List<NationalHoliday> nationalHolidays) {
    nationalHolidayRepository.saveAll(nationalHolidays);
}


}

Answer 1

Postgresql was out of sync. Query inside the table:

SELECT 
  setval(
    pg_get_serial_sequence('national_holiday', 'id'), 
    (
      SELECT 
        MAX(id) 
      FROM 
        national_holiday
    )+ 1
  );

reset the table, now it works fine.