[SOLVED] Resync Postgresql tables primary key

ERROR: duplicate key value violates unique constraint

me@jaykilleen.com wrote this about 3 years ago and it was last updated about 3 years ago.


← Back to the Posts

Replace the table reference users with whatever table you are having an issue with. This is postgresql so assumes all tables are under your public schema.

This first query returns the current last id used for the table

SELECT MAX(id) FROM public.users;

This next query returns what the database thinks is the next id to use... if this is lower than your max, your likely to see an error like ERROR: duplicate key value violates unique constraint "users_pkey"

SELECT nextval('public."users_id_seq"');

This last query is what I run to reset the tables id so that it is back in sync with the last id.

SELECT setval('public."users_id_seq"',
  (SELECT MAX(id) FROM public.users)
);