postgresql 如何在 postgres 中重置序列并用新数据填充 id 列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4678110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to reset sequence in postgres and fill id column with new data?
提问by sennin
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?
我有一个超过一百万行的表。我需要重置序列并使用新值（1、2、3、4...等...）重新分配 id 列。有什么简单的方法可以做到这一点吗？
回答by Michael Krelin - hacker
If you don't want to retain the ordering of ids, then you can
如果您不想保留 id 的顺序，那么您可以
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.
我怀疑是否有一种简单的方法可以按照您选择的顺序执行此操作，而无需重新创建整个表。
回答by Oliver
With PostgreSQL 8.4 or newer there is no need to specify the WITH 1anymore. The start value that was recorded by CREATE SEQUENCEor last set by ALTER SEQUENCE START WITHwill be used (most probably this will be 1).
使用 PostgreSQL 8.4 或更新版本，不再需要指定WITH 1。将使用记录者CREATE SEQUENCE或最后设置者的起始值ALTER SEQUENCE START WITH（很可能是 1）。
Reset the sequence:
重置序列：
ALTER SEQUENCE seq RESTART;
Then update the table's ID column:
然后更新表的 ID 列：
UPDATE foo SET id = DEFAULT;
Source: PostgreSQL Docs
回答by Frank Heikens
Reset the sequence:
重置序列：
SELECT setval('sequence_name', 0);
Updating current records:
更新当前记录：
UPDATE foo SET id = DEFAULT;
回答by ivy
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 1starts the numbering with 2 as well, because seq.is_called is true (Postgres version 9.0.4)
setval('seq', 1)以 2 开始编号，也以 2ALTER SEQUENCE seq START 1开始编号，因为 seq.is_called 为真（Postgres 9.0.4 版）
The solution that worked for me is:
对我有用的解决方案是：
> ALTER SEQUENCE seq RESTART WITH 1;
> UPDATE foo SET id = DEFAULT;
回答by Ali Raza Bhayani
Just for simplifying and clarifying the proper usage of ALTER SEQUENCEand SELECT setvalfor resetting the sequence:
只是为了简化和阐明ALTER SEQUENCE和SELECT setval用于重置序列的正确用法：
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 herealso:
无论是陈述的，可以使用重置序列，你可以得到由NEXTVAL（“SEQUENCE_NAME”）的下一个值规定在这里也：
nextval('sequence_name')
回答by jahmed31
The best way to reset a sequence to start back with number 1 is to execute the following:
重置序列以从数字 1 开始的最佳方法是执行以下操作：
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
回答by alexkovelsky
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;
回答by Stone
FYI: If you need to specify a new startvalue between a range of IDs (256 - 10000000 for example):
仅供参考：如果您需要在一系列 ID（例如 256 - 10000000）之间指定一个新的起始值：
SELECT setval('"Sequence_Name"',
(SELECT coalesce(MAX("ID"),255)
FROM "Table_Name"
WHERE "ID" < 10000000 and "ID" >= 256)+1
);
回答by Frank
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.
只是重置序列并更新所有行可能会导致重复的 id 错误。在许多情况下，您必须两次更新所有行。首先使用更高的 id 以避免重复，然后使用您实际想要的 id。
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:
请避免为所有 ID 添加固定金额（如其他评论中所建议）。如果您的行数超过此固定数量，会发生什么情况？假设序列的下一个值高于现有行的所有 id（您只想填补空白），我会这样做：
UPDATE table SET id = DEFAULT;
ALTER SEQUENCE seq RESTART;
UPDATE table SET id = DEFAULT;
回答by Diego Santa Cruz Mendezú
In my case, I achieved this with:
就我而言，我通过以下方式实现了这一目标：
ALTER SEQUENCE table_tabl_id_seq RESTART WITH 6;
Where my table is named table
我的桌子被命名为table
