sqlpostgresqldatabase-sequence

Reset Postgres auto-increment value to 0

I'm having a table where I sometimes enter data in inline editors where I manually add the id value which is usually auto-incremented.

In a case where id 4 was auto generated using scripts and then id 5 & 6 were added inline, whenever I run a insert query the db tries to auto-increment the value 4. But the value 5 has been added to database by a user using an inline editor. In such a case I used a script as below

SELECT SETVAL('<tableName>_id_seq', (SELECT MAX(id) FROM <tableName>));

Then the auto increment value gets assigned to the maximum id value the database currently contains thereby auto-incrementing for the insert queries successfully.

The issue I'm facing is when ever I clear the database, is there a method to reset the auto-increment value to 0 or something so the next insert queries will begin inserting with a value of 1 to the id column?

Solution

  • Seems I've found a handy answer for my question

    ALTER SEQUENCE <tableName>_id_seq RESTART;

    If you want to restart the id from a specific index (for example, you need to update the table packing_list and the column uid with the index 94), you can use the below query

    ALTER SEQUENCE packing_list_item_uid_seq RESTART WITH 94;

    Hope this helps