我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
克劳斯的答案是最有用的,除了有一点遗漏:你 必须在select语句中添加DISTINCT。
但是,如果您确定没有表+列名是等价的 对于两个不同的表,您还可以使用:
select sequence_name, --PG_CLASS.relname, PG_ATTRIBUTE.attname
reset_sequence(split_part(sequence_name, '_id_seq',1))
from PG_CLASS
join PG_ATTRIBUTE on PG_ATTRIBUTE.attrelid = PG_CLASS.oid
join information_schema.sequences
on information_schema.sequences.sequence_name = PG_CLASS.relname || '_' || PG_ATTRIBUTE.attname
where sequence_schema='public';
哪个是user457226的扩展方案适用的情况下 一些感兴趣的列名不是“ID”。
其他回答
这里有很多很好的答案。在重新加载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
我建议在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
原文(也修复了序列所有权)在这里
所以我可以告诉你,在这个帖子里没有足够的意见或重新发明的轮子,所以我决定让事情变得有趣。
下面是一个程序:
关注(仅影响)与表相关的序列 适用于串行和生成的AS身份列 适用于good_column_names和“BAD_column_123”名称 如果表为空,则自动分配相应序列定义的起始值 允许只影响特定的序列(在schema.table.column表示法中) 有预览模式
CREATE OR REPLACE PROCEDURE pg_reset_all_table_sequences(
IN commit_mode BOOLEAN DEFAULT FALSE
, IN mask_in TEXT DEFAULT NULL
) AS
$$
DECLARE
sql_reset TEXT;
each_sec RECORD;
new_val TEXT;
BEGIN
sql_reset :=
$sql$
SELECT setval(pg_get_serial_sequence('%1$s.%2$s', '%3$s'), coalesce(max("%3$s"), %4$s), false) FROM %1$s.%2$s;
$sql$
;
FOR each_sec IN (
SELECT
quote_ident(table_schema) as table_schema
, quote_ident(table_name) as table_name
, column_name
, coalesce(identity_start::INT, seqstart) as min_val
FROM information_schema.columns
JOIN pg_sequence ON seqrelid = pg_get_serial_sequence(quote_ident(table_schema)||'.'||quote_ident(table_name) , column_name)::regclass
WHERE
(is_identity::boolean OR column_default LIKE 'nextval%') -- catches both SERIAL and IDENTITY sequences
-- mask on column address (schema.table.column) if supplied
AND coalesce( table_schema||'.'||table_name||'.'||column_name = mask_in, TRUE )
)
LOOP
IF commit_mode THEN
EXECUTE format(sql_reset, each_sec.table_schema, each_sec.table_name, each_sec.column_name, each_sec.min_val) INTO new_val;
RAISE INFO 'Resetting sequence for: %.% (%) to %'
, each_sec.table_schema
, each_sec.table_name
, each_sec.column_name
, new_val
;
ELSE
RAISE INFO 'Sequence found for resetting: %.% (%)'
, each_sec.table_schema
, each_sec.table_name
, each_sec.column_name
;
END IF
;
END LOOP;
END
$$
LANGUAGE plpgsql
;
预览:
调用pg_reset_all_table_sequences ();
提交:
调用pg_reset_all_table_sequences(真正的);
只指定你的目标表:
调用pg_reset_all_table_sequences(“schema.table.column”);
此命令仅用于更改postgresql中自动生成的键序列值
ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;
在零的地方,你可以放任何你想重新启动序列的数字。
默认序列名为“TableName_FieldName_seq”。例如,如果您的表名是“MyTable”,字段名是“MyID”,那么序列名将是“MyTable_MyID_seq”。
这个答案与@murugesanponappan的答案相同,但在他的解决方案中有一个语法错误。在alter命令中不能使用sub query (select max()…)所以要么你必须使用固定的数值,要么你需要使用变量来代替子查询。
这个答案是从毛罗那里抄来的。
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();