How to fix Postgres sequences

Sometimes on your dev machine, postgres sequences of a table can become out of sync where the nextval returns an id that already exists.

To resolve sequence for a single table you can do following:

-- Select current Max ID from table
SELECT MAX(id) FROM table_name;

-- Select Next ID from table
SELECT nextval('table_name_id_seq');

-- Set Next ID Value to MAX ID
SELECT setval('table_name_id_seq', (SELECT MAX(id) FROM table_name));

If you run into an issue with above sql, because your column default is nextval('table_name_id_seq'::regclass) you can fix your sequence by following:

-- Select current Max ID from table
SELECT MAX(id) FROM table_name;

-- Select Next ID from table
SELECT nextval('table_name_id_seq'::regclass);

-- Set Next ID Value to MAX ID
SELECT setval('table_name_id_seq'::regclass, (SELECT MAX(id) FROM table_name));

If you want to fix all the sequences in your database in a single swoop, you can do following:

SELECT 'SELECT SETVAL(' ||
  quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
  ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
  quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

Ref: Official postgres documentation