74
[Fix] Rails Auto Increment ID Postgres Error
ActiveRecord::RecordNotUnique Exception: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "users_pkey"
DETAIL: Key (id)=(43957) already exists.
DETAIL: Key (id)=(43957) already exists.
You normally run into this error when you restore database from another source, for e.g. production or staging server.
This happens because of database sequence for Postgres that is stored in local machine is not the same as what comes from restored database and same id can be assigned twice when auto incrementing by Rails application.
We can reset the sequence of the table that is stored in the local machine by Postgres to fix this issue.
Go to rails console
rails c
Reset the Postgres sequence for the table
You can reset the Postgres sequence with the following command:
ActiveRecord::Base.connection.reset_pk_sequence!('table_name')
E.g. Assuming the table name is users, you can do the following:
ActiveRecord::Base.connection.reset_pk_sequence!('users')
After resetting the sequence of table stored by Postgres, new records will be created without any issues.
Thanks for reading. Happy Coding!
74