Postgresql unique violation on primary key when inserting record

2
votes

In Rails 4.2 with Postgresql I got this error when trying to save a new ActiveRecord object

PG::UniqueViolation: ERROR: Duplicate Key Value Violates Unique Constraint 'My_table_name_pkey'

It looks like it's trying to insert the record into the table with a primary key that already exists. How do I sort this out?

1
ruby-on-railspostgresql

1 Answers

6
votes

It turns out that somtimes the counter PG uses to generate primary keys can get messed up (I don't know how) and it justs needs to be reset to be the maximum value for the primary key in the table.

I found this answer in Jasith Fernando's blog

For me this was happening in the development database so I went into a console for that db like this:

rails db development

Then the name of the variable that needs resetting is the name of the table appended with _id_seq so the command to reset it to the maximum primary key value is this

SELECT setval('my_table_id_seq', (SELECT MAX(id) FROM my_table));