CODEWITHSUNDEEP
postgresqlprimary-keyreset
David
David

Reputation: 4465

Reset PostgreSQL primary key to 1

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.

Upvotes: 120

Views: 132430

Answers (6)

ZootHii
ZootHii

Reputation: 1040

TRUNCATE TABLE table_name RESTART IDENTITY;

This will delete all of your data.

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

Upvotes: 19

Arqam Rafay
Arqam Rafay

Reputation: 135

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

Upvotes: 2

Thangaraj
Thangaraj

Reputation: 73

@ZootHii TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;

Upvotes: -1

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340241

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

Upvotes: 41

ROQUEFORT Fran&#231;ois
ROQUEFORT Fran&#231;ois

Reputation: 71

@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.

Upvotes: 6

Paweł Gościcki
Paweł Gościcki

Reputation: 9604

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

Upvotes: 269

Related Questions