Fixing PG::UniqueViolation when there isn't a unique violation

· 2 min read

Have you ever encountered a PG::UniqueViolation error when you’re certain there’s no duplicate data? The culprit is often a PostgreSQL sequence that’s out of sync.

The Problem

You see an error like:

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

But when you check, there’s no record with ID 42. What’s happening?

The Cause

PostgreSQL uses sequences to generate auto-incrementing IDs. Sometimes, especially after data imports or manual inserts, the sequence gets out of sync with the actual maximum ID in the table.

The Fix

Reset the sequence to the correct value:

SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

Or in Rails:

ActiveRecord::Base.connection.execute(
  "SELECT setval('users_id_seq', (SELECT MAX(id) FROM users))"
)

For All Tables

To fix all sequences at once:

ActiveRecord::Base.connection.tables.each do |table|
  ActiveRecord::Base.connection.reset_pk_sequence!(table)
end

Prevention

This issue commonly occurs after:

  • Data migrations or imports
  • Manual SQL inserts with explicit IDs
  • Restoring from backups

Always reset sequences after bulk data operations to avoid these mysterious errors.