Reset PostgreSQL primary key to 1

Question

Is there a way to reset the primary key of a PostgreSQL table to start at 1 again on a populated table?

Right now it's generating numbers from 1000000 and up. I want it all to reset and start to 1, keeping all my existing data intact.

Paweł Gościcki · Answer 1 · 2011-12-11T13:11:30.257

246

The best way to reset a sequence to start back with number 1 is to execute the following:

ALTER SEQUENCE <tablename>_<id>_seq RESTART WITH 1

So, for example for the users table it would be:

ALTER SEQUENCE users_id_seq RESTART WITH 1

edited Dec 11 '11 at 13:11

answered Mar 11 '11 at 11:12

Paweł Gościcki

9,066
5
70
81

29

The `WITH 1` parameter is redundand and it can be ommited – Jacek Krawczyk Feb 03 '16 at 13:57
That's the only right answer as it really reset the sequence. – Balbinator Jul 06 '18 at 14:36
2

The **big problem** on indexes generated automatically by CREATE TABLE clause, is that we not know the name (and the suffix is not `seq`, can by `pk` etc.)... But it is easy by on psql by `\d my_table_name`. Or check by SQL, `SELECT * FROM pg_indexes WHERE tablename like '%my_table_name%'` – Peter Krauss Mar 20 '20 at 02:17
1

Just beat my head on this problem. If the table has data in it and you want it to start where it left off, you still want to pass `WITH 1` or leave it out. If you specify `WITH 123` or whatever the current highest value, it's going to ADD that value to your next row. Probably not what you want. – Owen Allen Aug 13 '21 at 21:30
1

I needed to prefix the sequence-identifier with the schema-name, otherwise i got "relation 'tablename_id_seq' does not exist". This naming convention can be looked up by "\d " and the default-value (nextval...) for the id column. So essentially in my case `ALTER SEQUENCE auth.roles_id_seq RESTART WITH 5;` – JackLeEmmerdeur Jan 24 '22 at 00:22

Vinko Vrsalovic · Accepted Answer · 2020-12-31T08:05:51.760

40

See a better option here: https://stackoverflow.com/a/5272164/5190

Primary keys that autoincrement (i.e., columns with data type serial primary key) are associated with a sequence. You can set the next value for any sequence using the setval(<seqname>, <next_value>) function. Note that to actually execute the function by itself you need to use SELECT, like this: SELECT setval(<seqname>, <next_value>)

The name of the auto created sequences when using serial are <table>_<column>_seq

edited Dec 31 '20 at 08:05

answered Sep 29 '10 at 06:41

Vinko Vrsalovic

330,807
53
334
373

14

You need to put "SELECT" in front of the call, e.g., `SELECT setval('table_id_seq', 10000)` – Tom Jul 17 '13 at 15:07
4

If I do `SELECT setval('table_id_seq', 1)`, when I insert a new record the id takes value 2, instead of 1. [Paweł Gościcki's solution](http://stackoverflow.com/a/5272164/505893) works. (PostgreSQL 9.3) – bluish Dec 11 '14 at 08:16
@bluish Presumably what happened is the sequence number is incremented before new primary key is assigned, ie. you'd need to reset it to 0. But I agree the other solution is better anyway. – kralyk Nov 06 '17 at 17:29

score 11 · Answer 3 · edited Feb 28 '23 at 17:09

11

TRUNCATE TABLE table_name RESTART IDENTITY;

This will delete all of your data.

Maybe some users looking for something like this as I am.

edited Feb 28 '23 at 17:09

brandonscript

68,675
32
163
220

answered Jun 28 '21 at 12:48

ZootHii

800
1
8
16

1

The fact that you have just copy-pasted this command ***without warning users that it will delete ALL ROWS in the table*** makes this a Very Bad "answer". Quite frankly, it should be deleted. I would down-vote it more if I could. – code_dredd Jun 02 '22 at 12:52

score 4 · Answer 4 · answered Jan 09 '18 at 15:13

@bluish actually inserting a new record using an auto-incremented primary key, is just like using a sequence explicitly this way:

INSERT INTO MyTable (id, col1, ...) VALUES (MySeq.nextval(), val1, ...)

So, if you want the first id to be 1, you should have to set your sequence to 0. But it's out of bounds, so you must use the ALTER SEQUECE statement. So, if you have a serial field named number in your table menu, for exemple:

ALTER SEQUENCE menu_number_seq RESTART

will make the job perfectly.

score 0 · Answer 5 · answered Dec 19 '22 at 06:29

0

Firstly, empty the table then reset the primary key.

let suppose table population you have.

delete from population

ALTER SEQUENCE population_id_seq" RESTART WITH 1

answered Dec 19 '22 at 06:29

Arqam Rafay

97
6

score -1 · Answer 6 · answered May 15 '22 at 12:49

-1

@ZootHii TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;

answered May 15 '22 at 12:49

Thangaraj

53
1
7

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/late-answers/31778600) – helvete May 20 '22 at 11:25
Same as I posted for ZootHii: The fact that you have just copy-pasted this command ***without warning users that it will delete ALL ROWS in the table*** makes this a Very Bad "answer". But this one is worse, because ***this one will CASCADE the data deletion to other tables that depend on it*** (e.g. by means of foreign keys). Again, this is another "answer" that should be deleted. I would down-vote it more if I could. – code_dredd Jun 02 '22 at 12:54
First thing, if try to truncate a table that has relation will not allow to truncate. – Thangaraj Jun 03 '22 at 13:53

Reset PostgreSQL primary key to 1

6 Answers6

This will delete all of your data.

Linked