How to Resync Your PostgreSQL Database Pointer

Correcting an out-of-sync database pointer will resolve a PostgreSQL UniqueViolation error,_CN_II.jpg

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

Photo of Dana Jones

Dana was born and raised in Dallas, Texas. She moved to eastern Washington after she married her husband, Mike, who is also a programmer. She now resides in Newburgh, Indiana with her husband and four children, ages ranging from 10-16.

Dana started programming in raw HTML and VBA, but moved on to C#/.NET. She did a six month stint writing Erlang, which she still applies to the way she approaches object-oriented programming. She has been writing Ruby on Rails since 2008 and was a founding board member of RailsBridge. After working freelance for many years and in the employment/product space for a couple more, Dana enjoys the challenges and variety that working for Collective Idea brings.

In her spare time Dana likes to read, sew, quilt, crochet, do puzzles, bake, and learn to play the violin. She also loves public speaking, going to conferences/meetups, getting to know new people, and learning about new technologies.