Duplicate key value violates unique constraint
This morning I had to migrate database to another for our team which needed those data to train models. After couple of minutes all went well, however after migration was done, team had reported problem with new database. After quick check I had find out that there is a problem inserting new data into only one specific table.
Problem
The moment I tried to insert new row into table there this error "Duplicate key value violates unique constraint...". Even if all values where empty or default same problem again and again.
Solution - Sequence
Solution for this problem is simple. Since sequesten data for this value were corrupted and sequence for this table had to be reset. This could be fixed using sequence sql method. And I dind't reset it to zero id since it would break database as well - there were existing records in database rather than choose lasts_record_id + 1.
So all you have to do is to reset sequence like this:
ALTER SEQUENCE config_id_seq RESTART WITH 2342
In my case table "config" had problem with column "id" so I had to use special varibale called "config_id_seq". Value 2342 was used becase 2341 was last record id in the database. I need to setup counter for value one higher to achieve this number in datase.
What is Sequence?
Database sequence is simple but important feature.
The sequence is a feature by some database products which just creates unique values. It just increments a value and returns it. The special thing about it is: there is no transaction isolation, so several transactions can not get the same value, the incrementation is also not rolled back.
Basicalli without a database sequence it would be very difficult to generate unique incrementing ids. Database products ids for columns automatically when created new record and incrementing the number.
"https://stackoverflow.com/questions/5342440/reset-auto-increment-counter-in-postgres"