Reset PostgreSQL primary key to 1

TRUNCATE TABLE table_name RESTART IDENTITY;

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

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

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

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