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

Question

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;

Just for simplifying and clarifying the proper usage of ALTER SEQUENCE and SELECT setval for resetting the sequence:

ALTER SEQUENCE sequence_name RESTART WITH 1;

is equivalent to

SELECT setval('sequence_name', 1, FALSE);

Either of the statements may be used to reset the sequence and you can get the next value by nextval('sequence_name') as stated here also:

nextval('sequence_name')

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

Both provided solutions did not work for me;

> SELECT setval('seq', 0);
ERROR:  setval: value 0 is out of bounds for sequence "seq" (1..9223372036854775807)

setval('seq', 1) starts the numbering with 2, and ALTER SEQUENCE seq START 1 starts the numbering with 2 as well, because seq.is_called is true (Postgres version 9.0.4)

The solution that worked for me is:

> ALTER SEQUENCE seq RESTART WITH 1;
> UPDATE foo SET id = DEFAULT;

To retain order of the rows:

UPDATE thetable SET rowid=col_serial FROM 
(SELECT rowid, row_number() OVER ( ORDER BY lngid) AS col_serial FROM thetable ORDER BY lngid) AS t1 
WHERE thetable.rowid=t1.rowid;