postgresql 不同步时如何重置postgres的主键序列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/244243/
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 postgres' primary key sequence when it falls out of sync?
提问by meleyal
I ran into the problem that my primary key sequence is not in sync with my table rows.
我遇到了我的主键序列与我的表行不同步的问题。
That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists.
也就是说，当我插入一个新行时，我得到一个重复键错误，因为串行数据类型中隐含的序列返回一个已经存在的数字。
It seems to be caused by import/restores not maintaining the sequence properly.
这似乎是由于导入/恢复没有正确维护序列造成的。
回答by meleyal
-- Login to psql and run the following
-- What is the result?
SELECT MAX(id) FROM your_table;
-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');
-- If it's not higher... run this set the sequence last to your highest id.
-- (wise to run a quick pg_dump first...)
BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;
回答by tardate
pg_get_serial_sequencecan be used to avoid any incorrect assumptions about the sequence name. This resets the sequence in one shot:
pg_get_serial_sequence可用于避免对序列名称的任何错误假设。这将一次性重置序列：
SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);
Or more concisely:
或者更简洁：
SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;
However this form can't handle empty tables correctly, since max(id) is null, and neither can you setval 0 because it would be out of range of the sequence. One workaround for this is to resort to the ALTER SEQUENCEsyntax i.e.
然而，这种形式不能正确处理空表，因为 max(id) 是空的，你也不能 setval 0 因为它会超出序列的范围。一种解决方法是诉诸ALTER SEQUENCE语法即
ALTER SEQUENCE table_name_id_seq RESTART WITH 1;
ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher
But ALTER SEQUENCEis of limited use because the sequence name and restart value cannot be expressions.
但ALTER SEQUENCE用途有限，因为序列名称和重新启动值不能是表达式。
It seems the best all-purpose solution is to call setvalwith false as the 3rd parameter, allowing us to specify the "next value to use":
似乎最好的通用解决方案是setval使用 false 作为第三个参数进行调用，允许我们指定“下一个要使用的值”：
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
This ticks all my boxes:
这勾选了我所有的框：
- avoids hard-coding the actual sequence name
- handles empty tables correctly
- handles tables with existing data, and does not leave a hole in the sequence
- 避免对实际序列名称进行硬编码
- 正确处理空表
- 处理包含现有数据的表，并且不会在序列中留下漏洞
Finally, note that pg_get_serial_sequenceonly works if the sequence is owned by the column. This will be the case if the incrementing column was defined as a serialtype, however if the sequence was added manually it is necessary to ensure ALTER SEQUENCE .. OWNED BYis also performed.
最后，请注意，pg_get_serial_sequence仅当序列由列拥有时才有效。如果将递增列定义为serial类型，就会出现这种情况，但是如果序列是手动添加的，则必须确保ALTER SEQUENCE .. OWNED BY也执行。
i.e. if serialtype was used for table creation, this should all work:
即如果serial类型用于表创建，这应该都有效：
CREATE TABLE t1 (
id serial,
name varchar(20)
);
SELECT pg_get_serial_sequence('t1', 'id'); -- returns 't1_id_seq'
-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
But if sequences were added manually:
但是如果序列是手动添加的：
CREATE TABLE t2 (
id integer NOT NULL,
name varchar(20)
);
CREATE SEQUENCE t2_custom_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval('t2_custom_id_seq'::regclass);
ALTER SEQUENCE t2_custom_id_seq OWNED BY t2.id; -- required for pg_get_serial_sequence
SELECT pg_get_serial_sequence('t2', 'id'); -- returns 't2_custom_id_seq'
-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t2', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
回答by Erwin Brandstetter
The shortest and fastestway:
在最短，最快的方法：
SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;
tbl_idbeing the serialcolumn of table tbl, drawing from the sequence tbl_tbl_id_seq(which is the default automatic name).
tbl_id作为serialtable的列tbl，从序列中绘制tbl_tbl_id_seq（这是默认的自动名称）。
Ifyou don't know the name of the attached sequence (which doesn't have to be in default form), use pg_get_serial_sequence():
如果您不知道附加序列的名称（不必采用默认形式），请使用pg_get_serial_sequence()：
SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;
There is no off-by-one error here. Per documentation:
这里没有逐一错误。根据文档：
The two-parameter form sets the sequence's
last_valuefield to the specified value and sets itsis_calledfield to true, meaning that the nextnextvalwill advance the sequencebefore returning a value.
二参数形式将序列的
last_value字段设置为指定值并将其is_called字段设置为 true，这意味着 下一个nextval将在返回值之前推进序列。
Bold emphasis mine.
大胆强调我的。
Ifthe table can be empty andto actually start from 1 in this case:
如果表可以是空的并且在这种情况下实际上从 1 开始：
SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id')
, COALESCE(max(tbl_id) + 1, 1)
, false)
FROM tbl;
We can't just use the 2-paremater form and start with 0because the lower bound of sequences is 1by default (unless customized).
我们不能只使用 2-paremater 形式并开始，0因为默认情况下序列的下限是1（除非定制）。
Concurrency
并发
There is no defense against concurrent sequence activity or writes to the table in the above queries, yet. If that's relevant, you might lock the tablein exclusive mode. It keeps concurrent transactions from writing a higher number while you are trying to get in sync. (It also temporarily blocks harmless writes not messing with the maximum number.)
目前还没有针对并发序列活动或上述查询中的表写入的防御措施。如果这是相关的，您可能会以独占模式锁定表。当您尝试同步时，它可以防止并发事务写入更高的数字。（它还会暂时阻止无害的写入，而不会影响最大数量。）
But it does not take clients into account that may have fetched sequence numbers in advance without any locks on the main table, yet (which can happen). To allow for that, too, only increasethe current value of the sequence, never decrease it. It may seem paranoid, but that's in accord with the nature of sequences and defending against concurrency issues.
但是它没有考虑可能已经提前获取序列号而主表上没有任何锁定的客户端（这可能会发生）。考虑到这一点，只增加序列的当前值，永远不要减少它。这可能看起来很偏执，但这符合序列的性质和防御并发问题。
BEGIN;
LOCK TABLE tbl IN EXCLUSIVE MODE;
SELECT setval('tbl_tbl_id_seq', max(tbl_id))
FROM tbl
HAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq);
COMMIT;
回答by djsnowsill
This will reset all sequences from public making no assumptions about table or column names. Tested on version 8.4
这将从 public 重置所有序列，不对表或列名做任何假设。在 8.4 版本上测试
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text) RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE 'SELECT setval( ''' || sequence_name || ''', ' || '(SELECT MAX(' || columnname || ') FROM ' || tablename || ')' || '+1)';
END;
$body$ LANGUAGE 'plpgsql';
select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name, table_name || '_' || column_name || '_seq') from information_schema.columns where column_default like 'nextval%';
回答by djsnowsill
ALTER SEQUENCE sequence_name RESTART WITH (SELECT max(id) FROM table_name);Doesn't work.
ALTER SEQUENCE sequence_name RESTART WITH (SELECT max(id) FROM table_name); 不起作用。
Copied from @tardate answer:
复制自@tardate 答案：
SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;
回答by Haider Ali Wajihi
This command for only change auto generated key sequence value in postgresql
此命令仅用于更改 postgresql 中自动生成的键序列值
ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;
In place of zero you can put any number from which you want to restart sequence.
代替零，您可以输入要从中重新启动序列的任何数字。
default sequence name will "TableName_FieldName_seq". For example, if your table name is "MyTable"and your field name is "MyID", then your sequence name will be "MyTable_MyID_seq".
默认序列名称将"TableName_FieldName_seq". 例如，如果您的表名是"MyTable"并且您的字段名是"MyID"，那么您的序列名称将是"MyTable_MyID_seq"。
This is answer is same as @murugesanponappan's answer, but there is a syntax error in his solution. you can not use sub query (select max()...)in altercommand. So that either you have to use fixed numeric value or you need to use a variable in place of sub query.
这个答案与@murugesanponappan 的答案相同，但他的解决方案中存在语法错误。您不能(select max()...)在alter命令中使用子查询。这样您要么必须使用固定数值，要么需要使用变量代替子查询。
回答by EB.
Reset all sequences, no assumptions about names except that the primary key of each table is "id":
重置所有序列，除了每个表的主键是“id”外，不对名称做任何假设：
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''' || columnname || '''),
(SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$body$ LANGUAGE 'plpgsql';
select table_name || '_' || column_name || '_seq', reset_sequence(table_name, column_name) from information_schema.columns where column_default like 'nextval%';
回答by alvherre
These functions are fraught with perils when sequence names, column names, table names or schema names have funny characters such as spaces, punctuation marks, and the like. I have written this:
当序列名称、列名称、表名称或模式名称具有有趣的字符（如空格、标点符号等）时，这些函数充满了危险。我写过这个：
CREATE OR REPLACE FUNCTION sequence_max_value(oid) RETURNS bigint
VOLATILE STRICT LANGUAGE plpgsql AS $$
DECLARE
tabrelid oid;
colname name;
r record;
newmax bigint;
BEGIN
FOR tabrelid, colname IN SELECT attrelid, attname
FROM pg_attribute
WHERE (attrelid, attnum) IN (
SELECT adrelid::regclass,adnum
FROM pg_attrdef
WHERE oid IN (SELECT objid
FROM pg_depend
WHERE refobjid =
AND classid = 'pg_attrdef'::regclass
)
) LOOP
FOR r IN EXECUTE 'SELECT max(' || quote_ident(colname) || ') FROM ' || tabrelid::regclass LOOP
IF newmax IS NULL OR r.max > newmax THEN
newmax := r.max;
END IF;
END LOOP;
END LOOP;
RETURN newmax;
END; $$ ;
You can call it for a single sequence by passing it the OID and it will return the highest number used by any table that has the sequence as default; or you can run it with a query like this, to reset all the sequences in your database:
您可以通过将 OID 传递给单个序列来调用它，它将返回任何具有该序列作为默认值的表使用的最大数字；或者您可以使用这样的查询运行它，以重置数据库中的所有序列：
select relname, setval(oid, sequence_max_value(oid))
from pg_class
where relkind = 'S';
Using a different qual you can reset only the sequence in a certain schema, and so on. For example, if you want to adjust sequences in the "public" schema:
使用不同的 qual，您只能重置特定模式中的序列，依此类推。例如，如果要调整“公共”模式中的序列：
select relname, setval(pg_class.oid, sequence_max_value(pg_class.oid))
from pg_class, pg_namespace
where pg_class.relnamespace = pg_namespace.oid and
nspname = 'public' and
relkind = 'S';
Note that due to how setval() works, you don't need to add 1 to the result.
请注意，由于 setval() 的工作方式，您不需要将 1 添加到结果中。
As a closing note, I have to warn that some databases seem to have defaults linking to sequences in ways that do not let the system catalogs have full information of them. This happens when you see things like this in psql's \d:
作为结束语，我必须警告一些数据库似乎默认链接到序列的方式不允许系统目录具有它们的完整信息。当您在 psql 的 \d 中看到类似的内容时，就会发生这种情况：
alvherre=# \d baz
Tabla ?public.baz?
Columna | Tipo | Modificadores
---------+---------+------------------------------------------------
a | integer | default nextval(('foo_a_seq'::text)::regclass)
Note that the nextval() call in that default clause has a ::text cast in addition to the ::regclass cast. I thinkthis is due to databases being pg_dump'ed from old PostgreSQL versions. What will happen is that the function sequence_max_value() above will ignore such a table. To fix the problem, you can redefine the DEFAULT clause to refer to the sequence directly without the cast:
请注意，该默认子句中的 nextval() 调用除了 ::regclass 类型转换之外还具有 ::text 类型转换。我认为这是由于旧 PostgreSQL 版本的数据库被 pg_dump'ed。将会发生的是上面的函数 sequence_max_value() 将忽略这样一个表。要解决此问题，您可以重新定义 DEFAULT 子句以直接引用序列而不进行强制转换：
alvherre=# alter table baz alter a set default nextval('foo_a_seq');
ALTER TABLE
Then psql displays it properly:
然后 psql 正确显示它：
alvherre=# \d baz
Tabla ?public.baz?
Columna | Tipo | Modificadores
---------+---------+----------------------------------------
a | integer | default nextval('foo_a_seq'::regclass)
As soon as you've fixed that, the function works correctly for this table as well as all others that might use the same sequence.
一旦您解决了这个问题，该函数就可以正确地用于该表以及可能使用相同序列的所有其他表。
回答by Vao Tsun
Yet another plpgsql - resets only if max(att) > then lastval
另一个 plpgsql - 仅在以下情况下重置 max(att) > then lastval
do --check seq not in sync
$$
declare
_r record;
_i bigint;
_m bigint;
begin
for _r in (
SELECT relname,nspname,d.refobjid::regclass, a.attname, refobjid
FROM pg_depend d
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
JOIN pg_class r on r.oid = objid
JOIN pg_namespace n on n.oid = relnamespace
WHERE d.refobjsubid > 0 and relkind = 'S'
) loop
execute format('select last_value from %I.%I',_r.nspname,_r.relname) into _i;
execute format('select max(%I) from %s',_r.attname,_r.refobjid) into _m;
if coalesce(_m,0) > _i then
raise info '%',concat('changed: ',_r.nspname,'.',_r.relname,' from:',_i,' to:',_m);
execute format('alter sequence %I.%I restart with %s',_r.nspname,_r.relname,_m+1);
end if;
end loop;
end;
$$
;
also commenting the line --execute format('alter sequencewill give the list, not actually resetting the value
也评论该行将--execute format('alter sequence给出列表，而不是实际重置值
回答by Pietro
I suggest this solution found on postgres wiki. It updates all sequences of your tables.
我建议在 postgres wiki 上找到这个解决方案。它会更新表的所有序列。
SELECT 'SELECT SETVAL(' ||
quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
pg_depend AS D,
pg_class AS T,
pg_attribute AS C,
pg_tables AS PGT
WHERE S.relkind = 'S'
AND S.oid = D.objid
AND D.refobjid = T.oid
AND D.refobjid = C.attrelid
AND D.refobjsubid = C.attnum
AND T.relname = PGT.tablename
ORDER BY S.relname;
How to use(from postgres wiki):
如何使用（来自 postgres wiki）：
- Save this to a file, say 'reset.sql'
- Run the file and save its output in a way that doesn't include the usual headers, then run that output. Example:
- 将此保存到文件中，例如“reset.sql”
- 运行文件并以不包含通常标题的方式保存其输出，然后运行该输出。例子：
Example:
例子：
psql -Atq -f reset.sql -o temp
psql -f temp
rm temp
Original article(also with fix for sequence ownership) here
原始文章（也修复了序列所有权）在这里
