我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
只需运行以下命令:
SELECT setval('my_table_seq', (SELECT max(id) FROM my_table));
其他回答
重置所有公共序列
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE 'SELECT setval( '''
|| tablename
|| '_id_seq'', '
|| '(SELECT id + 1 FROM "'
|| tablename
|| '" ORDER BY id DESC LIMIT 1), false)';
END;
$body$ LANGUAGE 'plpgsql';
select sequence_name, reset_sequence(split_part(sequence_name, '_id_seq',1)) from information_schema.sequences
where sequence_schema='public';
我花了一个小时试图让djsnowsill的答案与数据库使用Mixed Case表和列一起工作,然后终于偶然发现了解决方案,感谢Manuel Darveau的评论,但我认为我可以让每个人都更清楚一点:
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE format('SELECT setval(pg_get_serial_sequence(''%1$I'', %2$L),
(SELECT COALESCE(MAX(%2$I)+1,1) FROM %1$I), false)',tablename,columnname);
END;
$body$ LANGUAGE 'plpgsql';
SELECT format('%s_%s_seq',table_name,column_name), reset_sequence(table_name,column_name)
FROM information_schema.columns WHERE column_default like 'nextval%';
这样做的好处是:
不假设ID列是特定的拼写方式。 不假设所有的表都有一个序列。 适用于混合大小写表/列名。 使用更简洁的格式。
解释一下,问题是pg_get_serial_sequence接受字符串来计算你引用的是什么,所以如果你这样做:
"TableName" --it thinks it's a table or column
'TableName' --it thinks it's a string, but makes it lower case
'"TableName"' --it works!
这是使用格式字符串中的“%1$I”实现的,“使撇号1$表示第一个参数,I表示在引号中
我建议在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;
如何使用(来自postgres wiki):
保存到一个文件,输入'reset。sql' 运行该文件并以不包括通常头的方式保存其输出,然后运行该输出。例子:
例子:
psql -Atq -f reset.sql -o temp
psql -f temp
rm temp
原文(也修复了序列所有权)在这里
丑陋的黑客修复它使用一些贝壳魔法,不是一个伟大的解决方案,但可能会激励其他人有类似的问题:)
pg_dump -s <DATABASE> | grep 'CREATE TABLE' | awk '{print "SELECT setval(#" $3 "_id_seq#, (SELECT MAX(id) FROM " $3 "));"}' | sed "s/#/'/g" | psql <DATABASE> -f -
试着重建索引。
更新:正如评论中指出的,这是对最初问题的回答。