PostgreSQL Table Issue: Mduplicate key value violates unique constraint
It is possible for data within a PostgreSQL database table to get out of sync. When this happens it is possible for an attempt to be made to write data to a row in a table that already exists. This is being kept track of internally within Postgres by an ID of some sort and you can reset what the next ID that will be written to a row will be. Typically this will resolve your issue.
I received the below error in a Ruby on Rails application that was attempting to write data to PostgreSQL and at some point the data got out of wack.
- ActiveRecord::StatementInvalid (RuntimeError: ERROR C23505 Mduplicate key value violates unique constraint "kits_pkey" Fnbt
- insert.c L298 R_bt_check_unique: INSERT INTO "kits" ("updated_at", "created_at") VALUES('2009-10-05 20:22:55.068665', '2009-10
- -05 20:22:55.068665')):
- /vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:147:in `log'
- /vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:446:in `execute'
- /vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:161:in `insert_sql'
- /vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:44:in `insert_without_query_dir
As you can see in the example above the duplicate key error occurred on the “kits” table. So after seeing this error I viewed all of the data in the kits table to see what the last row was. In this case there were only 60 rows ending with id = 60. So I needed to tell PostgreSQL to start writing to the database with an id of 61 and to do so I issue the below command to Postgres on the psql CLI.
Alter PostgreSQL Sequence:
- alter sequence kits_id_seq restart with 61;
After issuing the above command to Postgres the Ruby application is now writing to the Postgres database without issue.