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.

Answer 1

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

Answer 2

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

Answer 3

TRUNCATE TABLE table_name RESTART IDENTITY;

This will delete all of your data. Maybe some users looking for something like this as I am.

Answer 4

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