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

postgresql sequence
sennin picture sennin · Jan 13, 2011 · Viewed 214.2k times · Source

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?

Answer

Michael Krelin - hacker picture Michael Krelin - hacker · Jan 13, 2011

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.

Related questions

PostgreSQL next value of the sequences?

I am using PostgreSQL for my Codeigniter website. I am using grocery crud for add, edit and delete operations. While doing an edit or add, I want to rename an uploaded file dynamically based on the id of the content. …

Read More
Why does PostgreSQL perform sequential scan on indexed column?

Very simple example - one table, one index, one query: CREATE TABLE book ( id bigserial NOT NULL, "year" integer, -- other columns... ); CREATE INDEX book_year_idx ON book (year) EXPLAIN SELECT * FROM book b WHERE b.year > 2009 gives …

Read More
Mapping PostgreSQL serial type with Hibernate annotations

I am using Hibernate 3.3 and PostgreSQL 8.x and would like to use Hibernate annotations to map an auto-incremented column which is NOT a primary key. It doesn't matter if the column is mapped using SERIAL type or sequences in Postgres …

Read More