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);
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.
Добрый день!
Очень странная ошибка вылезает исключительно при первом сохранении
SQLSTATE[23505]: Unique violation: 7 ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности "category_pkey" DETAIL: Ключ "(id)=(1)" уже существует. The SQL being executed was: INSERT INTO "module_category" ("name", "slug", "sort_order", "status", "created_at", "updated_at", "lft", "rgt", "depth") VALUES ('Пылесосы', 'pylesosy', 1, FALSE, 1531748095, 1531748095, 2, 3, 1) RETURNING "id"
Очень странные дела, он как будто пытается insert категорию, которая находится в _node
Сохранение происходит как написано в мануале:
$parent = Category::findOne($this->parent_id);
// ...
$category->appendTo($parent);
$category->save();
Ошибка возникает именно при самом первом сохранении после миграции.
PS: в миграции идет вставка «root» категории
PSS: https://monosnap.com/file/qHElE2SvMh2GCYsvrPUYNDjrA74U6t
PostgreSQL database 23505 is a common error which can be seen along with the error message «duplicate key violates unique constraint«.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related database queries.
In this context, we shall look into methods to resolve this PostgreSQL error.
Nature of PostgreSQL Error code 23505
Sometimes 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.
Here we have to manually reset the primary key index after restoring it from a dump file.
To check whether the values are out of sync, we can run the following commands:
SELECT MAX(the_primary_key) FROM the_table;
SELECT nextval('the_primary_key_sequence');
If the first value is higher than the second value, our sequence is out of sync.
We can back up our PG database and then run the following command:
SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);
This will set the sequence to the next available value that’s higher than any existing primary key in the sequence.
How to fix PostgreSQL Error code 23505 in VMware ?
When vpxd process crashes randomly after upgrading to vCenter Server 6.5 with the following error:
ODBC error: (23505) - ERROR: duplicate key value violates unique constraint "pk_vpx_guest_disk";
Panic: Unrecoverable database error. Shutting down VC
In the vpxd.log file, we can see entries similar to the one given below:
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";
–> Error while executing the query” is returned when executing SQL statement “INSERT INTO VPX_GUEST_DISK (VM_ID, PATH, CAPACITY, >FREE_SPACE) VALUES (?, ?, ?, ?)”
And in the postgresql.log file, you see entries similar to the one given below:
VCDB vc ERROR: duplicate key value violates unique constraint "pk_vpx_guest_disk"
VCDB vc DETAIL: Key (vm_id, path)=(976, /tmp) already exists.
vCenter Services can be given a service restart. If the starting fails to initialize the service and shows the same crash reason, we can fix this by removing the impacted guest disk entry from vCenter Server Database. This information is safe to remove as it will be re-populated from the host.
For vCenter Server with vPostgres database:
1. First, take a snapshot of the vCenter Server machine before proceeding.
2. Then connect the vCenter Database.
3. And identify the guest disk entry using the following query:
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';4. For deleting the duplicate value we can use the following command:
delete 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';We can get the VM id from the vpxd.log error entry
5. Finally start the Service.
We can delete the snapshot after observing the stability of the vCenter Server.
[Need assistance in fixing PostgreSQL Errors? We can help you. ]
Появился следующий вопрос в плане работы с PostgreSQL через Entity Framework.
Имеется следующая таблица:
CREATE TABLE "TankContentStateHistory" (
"TankContentStateHistoryId" integer NOT NULL SET DEFAULT nextval('"TankContentStateHistory_TankContentStateHistoryId_seq"'::regclass),
"AverageTemperature" double precision NOT NULL,
...
"GTVolume" double precision DEFAULT 0.0 NOT NULL);TankContentStateHistoryId берет свои значения из перечисления TankContentStateHistory_TankContentStateHistoryId_seq:
CREATE SEQUENCE "TankContentStateHistory_TankContentStateHistoryId_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;Значения должны быть все время положительными, но в последнее время стали появляться моменты, когда первичный ключ становится с отрицательным значением и откуда оно берется — не понятно. Логи PostgreSQL ясности не вносят:
2021-09-19 00:45:58 MSK ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности "PK_TankContentStateHistory"
2021-09-19 00:45:58 MSK ПОДРОБНОСТИ: Ключ "("TankContentStateHistoryId")=(-2147482602)" уже существует.
2021-09-19 00:45:58 MSK ОПЕРАТОР: INSERT INTO "TankContentStateHistory" ("TankContentStateHistoryId", ... "GTVolume")
VALUES ($1, ..)EntityFrameworkCore по сути дублирует тоже самое:
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.PostgresException: 23505: повторяющееся значение ключа нарушает ограничение уникальности "PK_TankContentStateHistory"
в Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()Вопрос в следующем — в каком месте может возникнуть проблема? Потому что после тщетных попыток уставить значение, все следующие значения ключа идет дальше по последовательности (например, id = 2044, потом отрицательное значение id=-454635, после id=2045).