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

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

Wondering how to fix PostgreSQL Error code 23505? We can help you.

One of the most common error codes with the PostgreSQL database is 23505. It can be seen along with the error message “duplicate key violates unique constraint”

Here at Bobcares, we often handle requests from our customers to fix similar PostgreSQL errors as a part of our Server Management Services. Today we will see how our support engineers fix this for our customers.

How PostgreSQL Error code 23505

At times we may get the following message when trying to insert data into a PostgreSQL database:

ERROR:  duplicate key violates unique constraint

This happens when the primary key sequence in the table we’re working on becomes out of sync. And this might likely be because of a mass import process.

SELECT MAX(the_primary_key) FROM the_table;

SELECT nextval('the_primary_key_sequence');

SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

ODBC error: (23505) - ERROR: duplicate key value violates unique constraint "pk_vpx_guest_disk";
Panic: Unrecoverable database error. Shutting down VC

error vpxd[7F8DD228C700] [Originator@6876 sub=InvtVmDb opID=HB-host-476@72123-38e1cc31] >[VpxdInvtVm::SaveGuestNetworkAndDiskToDb] Failed to insert guest disk info for VM id = 976because of database error: "ODBC error: >(23505) - ERROR: duplicate key value violates unique constraint "pk_vpx_guest_disk";

VCDB vc ERROR: duplicate key value violates unique constraint "pk_vpx_guest_disk"

VCDB vc DETAIL: Key (vm_id, path)=(976, /tmp) already exists.

select FROM vc.vpx_guest_disk where vm_id=<vm id> and path='/tmp';For example:

select FROM vc.vpx_guest_disk where vm_id='976' and path='/tmp';

delete FROM vc.vpx_guest_disk where vm_id=<vm id> and path='/tmp';

select FROM vc.vpx_guest_disk where vm_id='976' and path='/tmp';

5. Finally start the Service.

We can delete the snapshot after observing the stability of the vCenter Server.

[Need assistance? We can help you]

Conclusion

In short, we saw how our Support Techs fix PostgreSQL Error code 23505 for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;