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

Question

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

User · Answer

Just resetting the sequence and updating all rows may cause duplicate id errors. In many cases you have to update all rows twice. First with higher ids to avoid the duplicates, then with the ids you actually want.

Please avoid to add a fixed amount to all ids (as recommended in other comments). What happens if you have more rows than this fixed amount? Assuming the next value of the sequence is higher than all the ids of the existing rows (you just want to fill the gaps), i would do it like:

UPDATE table SET id = DEFAULT;
ALTER SEQUENCE seq RESTART;
UPDATE table SET id = DEFAULT;

User · Answer

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

User · Answer

In my case  I achieved this with   ALTER SEQUENCE table tabl id seq RESTART WITH 6    Where my table is named table

User · Answer

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

User · Answer

FYI  If you need to specify a new startvalue between a range of IDs  256 - 10000000 for example    SELECT setval   Sequence Name             SELECT coalesce MAX  ID   255              FROM  Table Name              WHERE  ID   lt  10000000 and  ID   gt   256  1

User · Answer

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

User · Answer

Even the auto-increment column is not PK   in this example it is called seq - aka sequence   you could achieve that with a trigger      DROP TABLE IF EXISTS devops guide CASCADE   SELECT  create the  devops guide  table       CREATE TABLE devops guide         guid           UUID NOT NULL DEFAULT gen random uuid         level          integer NULL       seq            integer NOT NULL DEFAULT 1       name           varchar  200  NOT NULL DEFAULT  name            description    text NULL       CONSTRAINT pk devops guide guid PRIMARY KEY  guid        WITH         OIDS FALSE         -- START trg devops guide set all seq CREATE OR REPLACE FUNCTION fnc devops guide set all seq       RETURNS TRIGGER     AS           BEGIN          UPDATE devops guide SET seq col serial FROM           SELECT guid  row number   OVER   ORDER BY seq  AS col serial FROM devops guide ORDER BY seq  AS tmp devops guide          WHERE devops guide guid tmp devops guide guid            RETURN NEW         END         LANGUAGE plpgsql    CREATE TRIGGER trg devops guide set all seq   AFTER UPDATE OR DELETE ON devops guide   FOR EACH ROW   WHEN  pg trigger depth    lt  1    EXECUTE PROCEDURE fnc devops guide set all seq

User · Answer

Reset the sequence   SELECT setval  sequence name   0     Updating current records   UPDATE foo SET id   DEFAULT

User · Answer

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

User · Answer

If you are using pgAdmin3  expand  Sequences   right click on a sequence  go to  Properties   and in the  Definition  tab change  Current value  to whatever value you want  There is no need for a query

User · Answer

Inspired by the other answers here  I created an SQL function to do a sequence migration  The function moves a primary key sequence to a new contiguous sequence starting with any value     1  either inside or outside the existing sequence range   I explain here how I used this function in a migration of two databases with the same schema but different values into one database   First  the function  which prints the generated SQL commands so that it is clear what is actually happening    CREATE OR REPLACE FUNCTION migrate pkey sequence     arg table      text     arg column     text     arg sequence   text     arg next value bigint  -- Must be  gt   1     RETURNS int AS    DECLARE   result int    curr value bigint   arg next value - 1    update column1 text    format        UPDATE  I SET  I   nextval  L     s        arg table       arg column       arg sequence       curr value          alter sequence text    format        ALTER SEQUENCE  I RESTART WITH  s        arg sequence       arg next value          update column2 text    format        UPDATE  I SET  I   DEFAULT        arg table       arg column          select max column text    format        SELECT coalesce max  I    s    1 AS nextval FROM  I        arg column       curr value       arg table        BEGIN   -- Print the SQL command before executing it    RAISE INFO      update column1    EXECUTE update column1    RAISE INFO      alter sequence    EXECUTE alter sequence    RAISE INFO      update column2    EXECUTE update column2    EXECUTE select max column INTO result    RETURN result  END    LANGUAGE plpgsql    The function migrate pkey sequence takes the following arguments    arg table  table name  e g   example   arg column  primary key column name  e g   id   arg sequence  sequence name  e g   example id seq   arg next value  next value for the column after migration   It performs the following operations    Move the primary key values to a free range  I assume that nextval  example id seq   follows max id  and that the sequence starts with 1  This also handles the case where arg next value  gt  max id   Move the primary key values to the contiguous range starting with arg next value  The order of key values are preserved but holes in the range are not preserved  Print the next value that would follow in the sequence  This is useful if you want to migrate the columns of another table and merge with this one    To demonstrate  we use a sequence and table defined as follows  e g  using psql      CREATE SEQUENCE example id seq   START WITH 1   INCREMENT BY 1   NO MINVALUE   NO MAXVALUE   CACHE 1      CREATE TABLE example     id bigint NOT NULL DEFAULT nextval  example id seq   regclass         Then  we insert some values  starting  for example  at 3      ALTER SEQUENCE example id seq RESTART WITH 3    INSERT INTO example VALUES  DEFAULT    DEFAULT    DEFAULT   -- id  3  4  5   Finally  we migrate the example id values to start with 1     SELECT migrate pkey sequence  example    id    example id seq   1   INFO   00000  UPDATE example SET id   nextval  example id seq     0 INFO   00000  ALTER SEQUENCE example id seq RESTART WITH 1 INFO   00000  UPDATE example SET id   DEFAULT  migrate pkey sequence -----------------------                      4  1 row    The result     SELECT   FROM example   id ----   1   2   3  3 rows

User · Answer

The best way to reset a sequence to start back with number 1 is to execute the following   ALTER SEQUENCE  lt tablename gt   lt id gt  seq RESTART WITH 1   So  for example for the users table it would be   ALTER SEQUENCE users id seq RESTART WITH 1

User · Answer

Both provided solutions did not work for me    gt  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    gt  ALTER SEQUENCE seq RESTART WITH 1   gt  UPDATE foo SET id   DEFAULT

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

Examples related to postgresql

Examples related to sequence