我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
重置所有序列,除了每个表的主键是“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%';
其他回答
ALTER SEQUENCE sequence_name RESTART WITH (SELECT max(id) FROM table_name); 是行不通的。
复制自@tardate答案:
SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;
重新启动所有序列到1使用:
-- Create Function
CREATE OR REPLACE FUNCTION "sy_restart_seq_to_1" (
relname TEXT
)
RETURNS "pg_catalog"."void" AS
$BODY$
DECLARE
BEGIN
EXECUTE 'ALTER SEQUENCE '||relname||' RESTART WITH 1;';
END;
$BODY$
LANGUAGE 'plpgsql';
-- Use Function
SELECT
relname
,sy_restart_seq_to_1(relname)
FROM pg_class
WHERE relkind = 'S';
此命令仅用于更改postgresql中自动生成的键序列值
ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;
在零的地方,你可以放任何你想重新启动序列的数字。
默认序列名为“TableName_FieldName_seq”。例如,如果您的表名是“MyTable”,字段名是“MyID”,那么序列名将是“MyTable_MyID_seq”。
这个答案与@murugesanponappan的答案相同,但在他的解决方案中有一个语法错误。在alter命令中不能使用sub query (select max()…)所以要么你必须使用固定的数值,要么你需要使用变量来代替子查询。
我花了一个小时试图让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表示在引号中
-- 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;
来源- Ruby论坛