How to reset Postgres SERIAL number

When testing a table with a SERIAL field, this number will grow even if you remove all items in the table (like you’d do during testing), so you might insert a value and its id is 15 for example.

If you want to remove every row from a test table and reset its owned sequence in one operation, use:

TRUNCATE TABLE users RESTART IDENTITY;

TRUNCATE is destructive and cannot be used when foreign-key references block it unless you deliberately handle those tables too.

To reset one known sequence directly:

ALTER SEQUENCE users_id_seq RESTART WITH 1;

Do this only when the table is empty. If rows still contain existing IDs, the next insert can collide with one of them.

When you need the sequence to continue after the current highest ID, use setval():

SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE((SELECT MAX(id) FROM users), 1),
  EXISTS (SELECT 1 FROM users)
);

Using TablePlus, you can choose to restart identities when truncating a table. Review the generated SQL before confirming it.