How do you reset a serial type back to 0 after deleting all rows in a table?
I previously asked https://dba.stackexchange.com/questions/294727/how-to-auto-increment-a-serial-column-on-update , and somebody posted a very helpful answer on db fiddle https://dbfiddle.uk/?rdbms=postgres_10&fiddle=871d2b1be9627095a15f07841dcb90b6 . 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 ?
Simple (assuming you know the sequence name):
SELECT setval('fruits_id_seq', 1, false);
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;