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;

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

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')

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;

SELECT SETVAL('seq_my_table_pk_id', (SELECT MAX(my_table_pk_id) + 1 FROM my_table));

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" < 10000000 and "ID" >= 256)+1
       ); 

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;

In my case, I achieved this with:

ALTER SEQUENCE table_tabl_id_seq RESTART WITH 6;

Where my table is named table

In my case sequences in all tables had been corrupted after importing the wrong sql file. SELECT nextval('table_name_id_seq'); was returning less than max value of the id column. So, I created sql script to recover all sequences for each table:

DO
$$
DECLARE
   rec  record;
   table_seq text;
BEGIN
   FOR rec IN
      SELECT *
      FROM   pg_tables
      WHERE  tablename NOT LIKE 'pg\_%'
      ORDER  BY tablename
   LOOP
      table_seq := rec.tablename || '_id_seq';
     
      RAISE NOTICE '%', table_seq;
      EXECUTE format(E'SELECT setval(\'%I\', COALESCE((SELECT MAX(id)+1 FROM %I), 1), false);',
            table_seq, rec.tablename);
      
   END LOOP;
END
$$;

Note: If you don't have the id column on any of your tables, you would eighter update the logic or handle them separately based on the logic above.

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.

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 >= 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:

1. arg_table: table name (e.g. 'example')
2. arg_column: primary key column name (e.g. 'id')
3. arg_sequence: sequence name (e.g. 'example_id_seq')
4. arg_next_value: next value for the column after migration

It performs the following operations:

1. 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 > max(id).
2. 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.
3. 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)

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() < 1)
  EXECUTE PROCEDURE fnc_devops_guide_set_all_seq();

For example, to update the value of sequence "SEQ_A" using the maximum value of the "FIELD_ID" field of table "TAB_B," you can use the following command:

SELECT setval('SEQ_A', (SELECT max(FIELD_ID) FROM TAB_B));

This command selects the maximum value of the "FIELD_ID" field of table "TAB_B" and sets it as the next value of sequence "SEQ_A."