How to reset sequence in postgres and fill id column with new data?

I have a table with over million rows. I need to reset sequence and reassign id column with new values (1, 2, 3, 4... etc...). Is any easy way to do that?


If you don't want to retain the ordering of ids, then you can

ALTER SEQUENCE seq RESTART WITH 1;
UPDATE t SET idcolumn=nextval('seq');

I doubt there's an easy way to do that in the order of your choice without recreating the whole table.


With PostgreSQL 8.4 or newer there is no need to specify the WITH 1 anymore. The start value that was recorded by CREATE SEQUENCE or last set by ALTER SEQUENCE START WITH will be used (most probably this will be 1).

Reset the sequence:

ALTER SEQUENCE seq RESTART;

Then update the table's ID column:

UPDATE foo SET id = DEFAULT;

Source: PostgreSQL Docs


Reset the sequence:

SELECT setval('sequence_name', 0);

Updating current records:

UPDATE foo SET id = DEFAULT;