我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
这个问题发生在我使用实体框架创建数据库,然后用初始数据播种数据库时,这使得序列不匹配。
我通过创建一个脚本来解决这个问题,在播种数据库后运行:
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$
其他回答
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;
这个答案是从毛罗那里抄来的。
drop function IF EXISTS rebuilt_sequences() RESTRICT;
CREATE OR REPLACE FUNCTION rebuilt_sequences() RETURNS integer as
$body$
DECLARE sequencedefs RECORD; c integer ;
BEGIN
FOR sequencedefs IN Select
DISTINCT(constraint_column_usage.table_name) as tablename,
constraint_column_usage.column_name as columnname,
replace(replace(columns.column_default,'''::regclass)',''),'nextval(''','') as sequencename
from information_schema.constraint_column_usage, information_schema.columns
where constraint_column_usage.table_schema ='public' AND
columns.table_schema = 'public' AND columns.table_name=constraint_column_usage.table_name
AND constraint_column_usage.column_name = columns.column_name
AND columns.column_default is not null
ORDER BY sequencename
LOOP
EXECUTE 'select max('||sequencedefs.columnname||') from ' || sequencedefs.tablename INTO c;
IF c is null THEN c = 0; END IF;
IF c is not null THEN c = c+ 1; END IF;
EXECUTE 'alter sequence ' || sequencedefs.sequencename ||' minvalue '||c ||' start ' || c ||' restart with ' || c;
END LOOP;
RETURN 1; END;
$body$ LANGUAGE plpgsql;
select rebuilt_sequences();
这里有很多很好的答案。在重新加载Django数据库后,我也有同样的需求。
但我需要:
All in one功能 可以一次修复一个或多个模式吗 可以修复所有或只是一个表的时间 还想要一个很好的方式来确切地看到什么改变了,或没有改变
这似乎与最初的要求非常相似。 多亏了Baldiry和Mauro让我找到了正确的方向。
drop function IF EXISTS reset_sequences(text[], text) RESTRICT;
CREATE OR REPLACE FUNCTION reset_sequences(
in_schema_name_list text[] = '{"django", "dbaas", "metrics", "monitor", "runner", "db_counts"}',
in_table_name text = '%') RETURNS text[] as
$body$
DECLARE changed_seqs text[];
DECLARE sequence_defs RECORD; c integer ;
BEGIN
FOR sequence_defs IN
select
DISTINCT(ccu.table_name) as table_name,
ccu.column_name as column_name,
replace(replace(c.column_default,'''::regclass)',''),'nextval(''','') as sequence_name
from information_schema.constraint_column_usage ccu,
information_schema.columns c
where ccu.table_schema = ANY(in_schema_name_list)
and ccu.table_schema = c.table_schema
AND c.table_name = ccu.table_name
and c.table_name like in_table_name
AND ccu.column_name = c.column_name
AND c.column_default is not null
ORDER BY sequence_name
LOOP
EXECUTE 'select max(' || sequence_defs.column_name || ') from ' || sequence_defs.table_name INTO c;
IF c is null THEN c = 1; else c = c + 1; END IF;
EXECUTE 'alter sequence ' || sequence_defs.sequence_name || ' restart with ' || c;
changed_seqs = array_append(changed_seqs, 'alter sequence ' || sequence_defs.sequence_name || ' restart with ' || c);
END LOOP;
changed_seqs = array_append(changed_seqs, 'Done');
RETURN changed_seqs;
END
$body$ LANGUAGE plpgsql;
然后执行并查看更改运行:
select *
from unnest(reset_sequences('{"django", "dbaas", "metrics", "monitor", "runner", "db_counts"}'));
返回
activity_id_seq restart at 22
api_connection_info_id_seq restart at 4
api_user_id_seq restart at 1
application_contact_id_seq restart at 20
选择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;';
我找不到Rails的明确答案。
从rails控制台
ActiveRecord::Base.connection.execute("SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;")
例如将table_name替换为users。