我遇到了一个问题,我的主键序列与我的表行不同步。

也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。

这似乎是由于导入/恢复没有正确地维护序列造成的。


当前回答

这个问题发生在我使用实体框架创建数据库,然后用初始数据播种数据库时,这使得序列不匹配。

我通过创建一个脚本来解决这个问题,在播种数据库后运行:

DO
$do$
DECLARE tablename text;
BEGIN
    -- change the where statments to include or exclude whatever tables you need
    FOR tablename IN SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name != '__EFMigrationsHistory'
        LOOP
            EXECUTE format('SELECT setval(pg_get_serial_sequence(''"%s"'', ''Id''), (SELECT MAX("Id") + 1 from "%s"))', tablename, tablename);
    END LOOP;
END
$do$

其他回答

只需运行以下命令:

SELECT setval('my_table_seq', (SELECT max(id) FROM my_table));

选择setval…使JDBC变得无聊,所以这里有一种与java兼容的方法:

-- work around JDBC 'A result was returned when none was expected.'
-- fix broken nextval due to poorly written 20140320100000_CreateAdminUserRoleTables.sql
DO 'BEGIN PERFORM setval(pg_get_serial_sequence(''admin_user_role_groups'', ''id''), 1 + COALESCE(MAX(id), 0), FALSE) FROM admin_user_role_groups; END;';

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;

这个问题发生在我使用实体框架创建数据库,然后用初始数据播种数据库时,这使得序列不匹配。

我通过创建一个脚本来解决这个问题,在播种数据库后运行:

DO
$do$
DECLARE tablename text;
BEGIN
    -- change the where statments to include or exclude whatever tables you need
    FOR tablename IN SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name != '__EFMigrationsHistory'
        LOOP
            EXECUTE format('SELECT setval(pg_get_serial_sequence(''"%s"'', ''Id''), (SELECT MAX("Id") + 1 from "%s"))', tablename, tablename);
    END LOOP;
END
$do$

我花了一个小时试图让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表示在引号中