Reset PostgreSQL primary key to 1

postgresql primary-key reset
David picture David · Sep 29, 2010 · Viewed 69.5k times · Source

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

Paweł Gościcki picture Paweł Gościcki · Mar 11, 2011

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

Related questions

How to reset postgres' primary key sequence when it falls out of sync?

I ran into the problem that my primary key sequence is not in sync with my table rows. That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype …

Read More
How to add an auto-incrementing primary key to an existing table, in PostgreSQL?

I have a table with existing data. Is there a way to add a primary key without deleting and re-creating the table?

Read More
Primary & Foreign Keys in pgAdmin

I was wondering can some give me an explanation on how to assign primary and foreign keys in pgAdmin? I can't find any information online. For example...I've got a Student table with all their details (address, d.o.b. …

Read More