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;