DEV Community

Cover image for [Fix] Rails Auto Increment ID Postgres Error
Prabin Poudel for Truemark Technology

Posted on

1

[Fix] Rails Auto Increment ID Postgres Error

Error

ActiveRecord::RecordNotUnique Exception: PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "users_pkey"
DETAIL: Key (id)=(43957) already exists.

Detail

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.

Solution

We can reset the sequence of the table that is stored in the local machine by Postgres to fix this issue.

  1. Go to rails console

    rails c

  2. 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')

Conclusion

After resetting the sequence of table stored by Postgres, new records will be created without any issues.

Thanks for reading. Happy Coding!

References

Image Credits

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more