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 генерируется. Проблема только когда запускаю на базе с тест. данными.

This question have been asked by several people but my problem seems to be different.
Actually I have to merge same structured tables from different databases in postgresql into a new DB. What I am doing is that I connect to remote db using dblink, reads that table in that db and insert it into the table in the current DB like below

INSERT INTO t_types_of_dementia SELECT * FROM dblink('host=localhost port=5432 dbname=snap-cadence password=xxxxxx', 'SELECT dementia_type, snapid FROM t_types_of_dementia') as x(dementia_type VARCHAR(256),snapid integer);

First time this query runs fine, but when I run it again or try to run it with some other remote database’s table: it gives me this error

ERROR: duplicate key value violates unique constraint
«t_types_of_dementia_pkey»

I want that this new tables gets populated by entries of others tables from other dbs.
Some of the solutions proposed talks about sequence, but i am not using any

The structure of the table in current db is

CREATE TABLE t_types_of_dementia(
    dementia_type VARCHAR(256),
    snapid integer NOT NULL,
    PRIMARY KEY (dementia_type,snapid)
);

P.S. There is a specific reason why both the columns are used as a primary key which may not be relevant in this discussion, because same issue happens in other tables where this is not the case.

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)» уже существует.

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

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "car")
public class CarEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    private String name;

    @OneToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "manufacture_id")
    private ManufactureEntity manufactureEntity;
}   

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "manufacture")
public class ManufactureEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
}

ManufactureEntity manufactureEntity = new ManufactureEntity();
manufactureEntity.setName("AUDI");
CarEntity car = new CarEntity();
car.setName("a6");
car.setManufactureEntity(manufactureEntity);
carService.create(car);

org.postgresql.util.PSQLException: ERROR: Duplicate key value violates the "manufacture_name_key" uniqueness constraint
Details: The key "(name)=(AUDI)" already exists.

The key "(name)=(AUDI)" already exists.

@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.MERGE)

manufactureEntity.setId(1L);

manufacture Entity.setName("AUDIM");

NULL value in the "manufacture_id" column

1 ответ