How to reset postgres' primary key sequence when it falls out of sync?

postgresql primary-key database-sequence
meleyal picture meleyal · Oct 28, 2008 · Viewed 280.8k times · Source

I ran into the problem that my primary key sequence is not in sync with my table rows.

That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists.

It seems to be caused by import/restores not maintaining the sequence properly.

Answer

meleyal picture meleyal · Oct 28, 2008 
-- Login to psql and run the following

-- What is the result?
SELECT MAX(id) FROM your_table;

-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');

-- If it's not higher... run this set the sequence last to your highest id. 
-- (wise to run a quick pg_dump first...)

BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

Source - Ruby Forum

Related questions

How to add an auto-incrementing primary key to an existing table, in PostgreSQL?

I have a table with existing data. Is there a way to add a primary key without deleting and re-creating the table?

Read More
Reset PostgreSQL primary key to 1

Is there a way to reset the primary key of a PostgreSQL table to start at 1 again on a populated table? Right now it's generating numbers from 1000000 and up. I want it all to reset and start to 1, keeping all …

Read More
Primary & Foreign Keys in pgAdmin

I was wondering can some give me an explanation on how to assign primary and foreign keys in pgAdmin? I can't find any information online. For example...I've got a Student table with all their details (address, d.o.b. …

Read More