Psqlexception ошибка повторяющееся значение ключа нарушает ограничение уникальности

Intro

I also encountered this problem and the solution proposed by @adamo was basically the right solution. However, I had to invest a lot of time in the details, which is why I am now writing a new answer in order to save this time for others.

Case

My case was as follows: There was a table that was filled with data using an app. Now a new entry had to be inserted manually via SQL. After that the sequence was out of sync and no more records could be inserted via the app.

Solution

As mentioned in the answer from @adamo, the sequence must be synchronized manually. For this purpose the name of the sequence is needed. For Postgres, the name of the sequence can be determined with the command PG_GET_SERIAL_SEQUENCE. Most examples use lower case table names. In my case the tables were created by an ORM middleware (like Hibernate or Entity Framework Core etc.) and their names all started with a capital letter.

In an e-mail from 2004 (link) I got the right hint.

(Let’s assume for all examples, that Foo is the table’s name and Foo_id the related column.)

Command to get the sequence name:

SELECT PG_GET_SERIAL_SEQUENCE('"Foo"', 'Foo_id');

So, the table name must be in double quotes, surrounded by single quotes.

1. Validate, that the sequence is out-of-sync

SELECT CURRVAL(PG_GET_SERIAL_SEQUENCE('"Foo"', 'Foo_id')) AS "Current Value", MAX("Foo_id") AS "Max Value" FROM "Foo";

When the Current Value is less than Max Value, your sequence is out-of-sync.

2. Correction

SELECT SETVAL((SELECT PG_GET_SERIAL_SEQUENCE('"Foo"', 'Foo_id')), (SELECT (MAX("Foo_id") + 1) FROM "Foo"), FALSE);

Здравствуйте у меня следущая проблема :

Есть back-end сервер который написанный в Spring и работает с postgresql базой. Сначала всё работало нормально и никаких проблем не было. Но сейчас друг с которым работаем на проекте сделал sql скрипт с помощью которого мы просто записываем тестовые данные в нашу базу данных. И вот тогда у нас и случилась беда :

19:46:30,253 INFO  [stdout] (default task-6) Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "user_pkey"
19:46:30,254 INFO  [stdout] (default task-6)   Detail: Key (id)=(1) already exists.
19:46:30,254 INFO  [stdout] (default task-6)    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497)
19:46:30,254 INFO  [stdout] (default task-6)    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233)
19:46:30,254 INFO  [stdout] (default task-6)    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
19:46:30,254 INFO  [stdout] (default task-6)    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
19:46:30,254 INFO  [stdout] (default task-6)    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
19:46:30,255 INFO  [stdout] (default task-6)    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
19:46:30,255 INFO  [stdout] (default task-6)    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124)
19:46:30,255 INFO  [stdout] (default task-6)    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
19:46:30,256 INFO  [stdout] (default task-6)    ... 158 more

В том скрипте у нас также есть какие-то тестовые пользователи и эта ошибка вылетает при регистрации. Я понимаю что эта ошибка говорит что нельзя сохранить пользователя т.к. такой id в базе данных уже есть. Но загвоздка в том что я не присваиваю id для пользователя а делает это сам Hibernate. Тоисть когда я посылаю пользователя в методу save() то посылаю его без id и Hibernate сам должен присвоить ему этот id с учетом на стратегию которую я выбрал. По крайней мере я надеюсь что так оно должно работать и так оно работало до того как мы записали тестовые данные в базу данных. Но я всегда думал что когда Hibernate выбирает id то он смотрит в базе данных в таблицу или в какую-то user_id_sequence какой id был последний и берет последний + 1. И вот вопрос, почему сейчас так не происходит и Hibernate пробует сохранить пользователя с id = 1, когда в базе уже такой пользователь есть. Как это можно исправить / решить проблему ?

Если что вот так у нас стоит стратегия для генерирования id :

@Getter
@Setter
@MappedSuperclass
@EqualsAndHashCode
@NoArgsConstructor
@ToString(exclude = {"deleted", "updated"})
public class BaseEntity {

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

    // ...
}

Конфигурация Hibernate :

datasource.url=**********
datasource.username=**********
datasource.password=**********
datasource.driver=org.postgresql.Driver
hibernate.show_sql=false
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.batch.size=200
hibernate.hbm2ddl.auto=update

Sql скрипт выглядит как обычный скрипт для наполнения :

INSERT INTO public.country (id, created, deleted, updated, code, name, telephone_prefix, continent_id) VALUES (101, NULL, false, NULL, 'ISR', 'Israel', 972, NULL);
....

Update :

Ещё хотел бы добавить что когда запускаю на пустой базе данных без тест. данных то всё работает как надо и id генерируется. Проблема только когда запускаю на базе с тест. данными.

package com.company.Labs.Lab_6;

import java.sql.*;


public class Final_test {
    public void ex1() {

        try {
            //Задание 1
            Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/testDB", "postgres", "1703");
            Statement statement = connection.createStatement();


            statement.execute("drop table if exists students");
            statement.execute("create table students (ID int primary key not null, NAME varchar(20) not null" +
                    ", AGE int not null , STUDY_OR_NOT boolean not null, HEIGHT float not null)");

            PreparedStatement preparedStatement = connection.prepareStatement("insert into students (ID,NAME,AGE,STUDY_OR_NOT,HEIGHT) values (?,?,?,?,?)");

            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, "Daniil");
            preparedStatement.setInt(3, 20);
            preparedStatement.setBoolean(4, true);
            preparedStatement.setFloat(5, 1.82f);

            int n = preparedStatement.executeUpdate();
            System.out.println(n); // выводит 1, то есть все крашится на том, что id, якобы повторяется и я его будто 2 раза записываю.


            ResultSet resultSet = statement.executeQuery("select * from students");
            while (resultSet.next()) {

                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                int age = resultSet.getInt(3);
                boolean study_or_not = resultSet.getBoolean(4);
                float height = resultSet.getFloat(5);
                preparedStatement.executeUpdate();

                System.out.println(id + "/" + name + "/" + age + "/" + study_or_not + "/" + height);
            }

            statement.close();
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            System.err.println(e);
        }

    }
}

org.postgresql.util.PSQLException: ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности «students_pkey»
Подробности: Ключ «(id)=(1)» уже существует.

по мне так это странно, ведь я задаю только один раз для каждого поля значение, а потом один раз их считываю. Вообщем странная тема.