Fixing: PG::UniqueViolation: ERROR: Duplicate Key Value Violates Unique Constraint 'Your_table_name_pkey'

Issue

Postgresql maintain a internal value which is the max value of the id column of a given table. And uses this to generate values for primary keys when you insert new records. Sometimes this internally stored value can get messed up. And that can cause the error above when you are trying to insert a new record to the table.

Fix

You need to basically correct the above said value.

[fix]
1
2
3
pg_dump -U user_name database_name -f database_backup_name.sql
cd /your/rails/app/root/
rails db production
[update your_table_id_sql]
1
SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table));

replace your_table with whatever the table name you are using. And the code above assumes your primary key column name is id.

References

Comments