As consultants we work on a variety of applications and with many types of databases. Here at Collective Idea we are fortunate to have our clients’ - and our own - applications backed by everything from MySQL to MongoDB to PostgreSQL, and more. Working with so many DBMSes familiarizes us with many of the idiosyncrasies of each.

Recently, we came across a situation we’ve seen before where the auto incrementing, primary key pointer lags behind the rows in the database, resulting in a nasty UniqueValidation error.

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

Essentially, the PostgreSQL database is trying to assign the unique key (id) of a row that already exists to a new row it’s trying to insert. This can happen when you try to import rows into the table without correctly maintaining the table’s _id_seq.

The fix for this issue was minor, but it was non-intuitive enough that we wanted to share.

First, you’ll want to verify that you’re solving the right problem. Let’s say you’re getting the UniqueValidation error when you try to insert a new record into the users table.

SELECT MAX(id) FROM users;

This returns the highest id you have for rows currently in your users table. Now, check the table’s _id_seq value.

SELECT nextval('users_id_seq');

This indicates the id that will be assigned when you insert a new row into the users table. It should be higher that the number you got from the previous command. If it isn’t, you need to resync the pointer to work with your database in its current state.

SELECT setval('users_id_seq',
COALESCE((SELECT MAX(id)+1 FROM users), 1), false);

This sets the users_id_seq value to either 1 (if there are no rows in the table), or to 1 greater than the highest id currently in the table.

Once you’ve resync’ed your pointer, you should be able to move on to inserting rows into your database.

---

Rubik’s Cube, CN II by Carschten is licensed under CC BY-SA 3.0