Reset Postgres auto-increment value to 0

Question

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?

What's wrong with using setval('..', 0)?

user330315
– user330315

2020-05-25 21:13:32 +00:00
Commented May 25, 2020 at 21:13 — user330315
– user330315, Commented May 25, 2020 at 21:13

Charith Jayasanka · Accepted Answer · 2022-11-03 19:59:16Z

3

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

edited Nov 3, 2022 at 19:59

answered May 25, 2020 at 21:22

Charith Jayasanka

4,9222 gold badges38 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Danizavtz · Accepted Answer · 2020-05-25 21:19:51Z

1

Here is an example of what i do i my sql to restart the index from my database. I use this code when resetting the entities for unit testing my api.

In my database i have a table terminal:

CREATE TABLE IF NOT EXISTS terminal(
    id SERIAL PRIMARY KEY,
    "serialNumber" VARCHAR(255),
    "storeCode" VARCHAR(100),
    "modelCode" VARCHAR(31)
);

It will create a table with a primary key terminal_id_seq.

ALTER SEQUENCE terminal_id_seq RESTART WITH 1;
DELETE FROM terminal;
--redo the inserts
INSERT INTO terminal ("id", "serialNumber", "storeCode","modelCode") VALUES (10,'abc','def','blabla');

Hope it helps.

answered May 25, 2020 at 21:19

Danizavtz

3,3214 gold badges27 silver badges28 bronze badges

1 Comment

Charith Jayasanka Over a year ago

ALTER SEQUENCE terminal_id_seq RESTART WITH 1; This line was what I wanted. Thanks

xzilla · Accepted Answer · 2020-05-25 23:37:35Z

1

There are a number of ways to do this as outlined above, but if you want a more idempotent way to do it (which you do), then I'd recommend:

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

This way you can use the same code whether you have rows in the table or not, and it will do the right thing.

answered May 25, 2020 at 23:37

xzilla

1,1619 silver badges19 bronze badges

Collectives™ on Stack Overflow

Reset Postgres auto-increment value to 0

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related