How do you reset a serial type back to 0 after deleting all rows in a table?

Question

I previously asked this question , and somebody posted a very helpful answer on db fiddle here. This has worked really well for me. However, now I would like to ask a new (related) question :

Suppose I wipe out all the rows in the table (i.e. in the db fiddle example the apple and orange rows are deleted by doing "delete from fruits where id > 0"). If I insert another fruit like watermelon, the id will start at 4, not 1. How do I force the id type to reset back to 0, so that the next insert will have id=1 ?

Erwin Brandstetter · Accepted Answer · 2023-10-26T09:35:05.090

Simple (assuming you know the sequence name):

SELECT setval('fruits_id_seq', 1, false);

Or:

ALTER SEQUENCE payments_id_seq RESTART WITH 0;

Safe (you don't know the sequence name or are not certain whether the table is empty):

SELECT setval(pg_get_serial_sequence('fruits', 'id')
            , COALESCE(max(id) + 1, 1)
            , false)
FROM   fruits;

db<>fiddle here

Of course, that's still not safe under concurrent write load - where you shouldn't mess with the sequence like this at all.

See:

How do you reset a serial type back to 0 after deleting all rows in a table?

1 Answers1