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

Question

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 1

Try reindex.

Answer 2

// 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 to try and fix it. (run a quick pg_dump first...)
SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table)+1);

Source - Ruby Forum

Answer 3

ALTER SEQUENCE sequence_name RESTART WITH (SELECT max(id) FROM table_name);

Answer 4

htxts method worked great for me. Thanks

Answer 5

pg_get_serial_sequence can be used to avoid any incorrect assumptions about the sequence name. This resets the sequence in one shot:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);

Answer 6

Reset all sequence from public

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) RETURNS "pg_catalog"."void" AS 
$body$  
  DECLARE 
  BEGIN 
  EXECUTE 'SELECT setval( ''' 
  || tablename  
  || '_id_seq'', ' 
  || '(SELECT id + 1 FROM "' 
  || tablename  
  || '" ORDER BY id DESC LIMIT 1), false)';  
  END;  
$body$  LANGUAGE 'plpgsql';

select sequence_name, reset_sequence(split_part(sequence_name, '_id_seq',1)) from information_schema.sequences
        where sequence_schema='public';