postgresql - Rails PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "table_pkey"

Question

I created something like a JSON backup for my project's database, and then I populate it like this

Model.find_or_initialize_by(:id => h["id"]).update(h)}

being h a hash of the model attributes for an instance.

The records are effectively created, but when I want to create a new record, rails rises this error

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "table_pkey"

What could I be doing wrong? It happens for all models which were created using scaffold, here a migration as an example.

class CreateModel < ActiveRecord::Migration[6.1]
  def change
    create_table :models do |t|
      t.string :attribute1
      t.string :attribute2
      t.string :attribute3

      t.timestamps
    end
  end
end

question from:https://stackoverflow.com/questions/65850264/rails-pguniqueviolation-error-duplicate-key-value-violates-unique-constraint

Answer 1

You're using sequential integer id's for your table according to the migration. This works well enough if you allow the database to assign id's for you. Every time a new record comes in, database takes the next number on the list and assigns it to that record (simplifying here).

Lets assume the database id sequence is currently at 3 and the records you imported have ids 4, 37 and 143025. Inserting a new record to the database, database says id is 3, all good, sequence is now at 4. Inserting another one, database says id is 4. Trying to insert it, but there already is a 4 in the database.

PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "table_pkey"

A few possible solutions:

1. After importing, change the database id sequence to something bigger than the largest id you imported. (hacky, but works) Postgres manually alter sequence
2. Import the items without hardcoding their id-s. (complicated)
3. Change your database to use uuid-s instead of integer id-s (architectural change, difficult if the app is live, best solution if you're still in development)
4. Use a proper database backup system rather than building your own. pg_dump