How to reset sequence in postgres and fill id column with new data?
PostgresqlSequencePostgresql Problem Overview
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?
Postgresql Solutions
Solution 1 - Postgresql
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.
Solution 2 - Postgresql
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
Solution 3 - Postgresql
Reset the sequence:
SELECT setval('sequence_name', 0);
Updating current records:
UPDATE foo SET id = DEFAULT;
Solution 4 - Postgresql
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')
Solution 5 - Postgresql
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
Solution 6 - Postgresql
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;
Solution 7 - Postgresql
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;
Solution 8 - Postgresql
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
);
Solution 9 - Postgresql
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;
Solution 10 - Postgresql
In my case, I achieved this with:
ALTER SEQUENCE table_tabl_id_seq RESTART WITH 6;
Where my table is named table
Solution 11 - Postgresql
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.
Solution 12 - Postgresql
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:
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')
followsmax(id)
and that the sequence starts with 1. This also handles the case wherearg_next_value > 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)
Solution 13 - Postgresql
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();
Solution 14 - Postgresql
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.
Solution 15 - Postgresql
SELECT SETVAL('seq_my_table_pk_id', (SELECT MAX(my_table_pk_id) + 1 FROM my_table));