Reset Sequence in Postgres

#postgres

A simple command to update Sequence in your Postgres table:

SELECT concat('SELECT setval(''', pg_get_serial_sequence(tc.table_name, kcu.column_name), '''::regclass, (SELECT COUNT(id) + 1 FROM ', tc.table_name, '), false);') AS query
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY' AND kcu.column_name = 'id' and pg_get_serial_sequence(tc.table_name, kcu.column_name) != '';

Copy all the output and run each of it. That's all you need.

Update: 29-07-2023

In case you need to be based on value of last id instead of total record, you can update the above query as following:

SELECT concat('SELECT setval(''', pg_get_serial_sequence(tc.table_name, kcu.column_name), '''::regclass, (SELECT id + 1 FROM ', tc.table_name, ' ORDER BY id desc limit 1), false);') AS query
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'PRIMARY KEY' AND kcu.column_name = 'id' and pg_get_serial_sequence(tc.table_name, kcu.column_name) != '';