Recently I was getting an issue while inserting a new record to one of the table in our database. I was getting this specific error -
ActiveRecord::RecordNotUnique (PG::Error: ERROR: duplicate key value violates unique constraint "canonical_cities_pkey"
DETAIL: Key (id)=(*****) already exists.
Understanding the error
The table currently has several thousand entities for canonical cities. But when am trying to insert a new record it builds an canonical_city record and assign it an 'id', say 111, but that 'id' already exist. The primary key which is suppose to auto-increment and generate an unique id is not working properly.
Primary key sequence - responsible for generating an unique primary key for a next record.
On checking, we found primary key sequence was impacted.
What created this weird error
As the table 'canonical_cities' had several thousand records, we copied the data dump from development to our deployment environments - this resulted in primary key sequence setting going wrong. It was set to last value added before we copied the dump.
Solutions
To fix the primary key sequence - we tried the first solution - but there is one more alternative, please check both solution 1 and 2:
First solution
- Start the rails console
- Execute couple of lines
connection = ActiveRecord::Base.connection
connection.execute("SELECT setval(pg_get_serial_sequence('canonical_cities', 'id'), MAX(id)) FROM canonical_cities;")
Second solution - this will correct primary key sequence for all tables -
ActiveRecord::Base.connection.tables.each { |t| ActiveRecord::Base.connection.reset_pk_sequence!(t) }
Hope it helps.