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

Postgres handles auto incrementing a little differently than MySQL does. In Postgres, when you create the serial field, you are also creating a sequence field that is keeping track of the id to use. This sequence field is going to start out with a value of 1.

When you insert a new record into the table, if you don’t specify the id field, it will use the value of the sequence, and then increment the sequence. However, if you do specify the id field, then the sequence is not used, and it is not updated, either.

I’m assuming that when you moved over to Postgres, you seeded or imported some existing users, along with their existing ids. When you created these user records with their ids, the sequence was not used, and therefore it was never updated.

So, if, for example, you imported 10 users, you have users with ids 1-10, but your sequence is still at 1. When you attempt to create a new user without specifying the id, it pulls the value from the sequence (1), and you get a unique violation because you already have a user with id 1.

To resolve the issue, you need to set your users_id_seq sequence value to the MAX(id) of your existing users. You can read this question/answer for more information on resetting the sequence, but you can also try something like (untested):

SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id)+1, 1), false) FROM users;

FYI, this is not an issue in MySQL because MySQL automatically updates the auto increment sequence to the largest column value when a value is manually inserted into the auto incrementing field.

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.

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.

Главная

Я также столкнулся с этой проблемой, и решение, предложенное @adamo, было в основном правильным решением. Однако мне пришлось потратить много времени на детали, поэтому сейчас я пишу новый ответ, чтобы сэкономить это время для других.

Коробка

Мой случай был следующим: была таблица, которая была заполнена данными с помощью приложения. Теперь новую запись нужно было вставить вручную через SQL. После этого последовательность не синхронизировалась, и больше нельзя было вставить записи через приложение.

Решения

Как упоминалось в ответе @adamo, последовательность необходимо синхронизировать вручную. Для этого необходимо название последовательности. Для Postgres имя последовательности можно определить с помощью команды PG_GET_SERIAL_SEQUENCE. В большинстве примеров используются имена таблиц в нижнем регистре. В моем случае таблицы были созданы промежуточным программным обеспечением ORM (например, Hibernate или Entity Framework Core и т. Д.), И все их имена начинались с заглавной буквы.

В электронном письме от 2004 г. (ссылке) Я получил правильный намек.

(Предположим для всех примеров, что Foo это имя таблицы и Foo_id соответствующий столбец.)

Команда для получения названия последовательности:

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

Итак, имя таблицы должно быть в двойных кавычках, окруженных одинарными кавычками.

1. Убедитесь, что последовательность не синхронизирована.

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

Когда Current Value меньше чем Max Value, ваша последовательность не синхронизирована.

2. Исправление

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