我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
这个答案是从毛罗那里抄来的。
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();
其他回答
我花了一个小时试图让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表示在引号中
还有另一个plpgsql -仅当max(att) >时重置lastval
do --check seq not in sync
$$
declare
_r record;
_i bigint;
_m bigint;
begin
for _r in (
SELECT relname,nspname,d.refobjid::regclass, a.attname, refobjid
FROM pg_depend d
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
JOIN pg_class r on r.oid = objid
JOIN pg_namespace n on n.oid = relnamespace
WHERE d.refobjsubid > 0 and relkind = 'S'
) loop
execute format('select last_value from %I.%I',_r.nspname,_r.relname) into _i;
execute format('select max(%I) from %s',_r.attname,_r.refobjid) into _m;
if coalesce(_m,0) > _i then
raise info '%',concat('changed: ',_r.nspname,'.',_r.relname,' from:',_i,' to:',_m);
execute format('alter sequence %I.%I restart with %s',_r.nspname,_r.relname,_m+1);
end if;
end loop;
end;
$$
;
另外注释行——execute format('alter sequence将给出列表,而不是实际重置值
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;
此命令仅用于更改postgresql中自动生成的键序列值
ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;
在零的地方,你可以放任何你想重新启动序列的数字。
默认序列名为“TableName_FieldName_seq”。例如,如果您的表名是“MyTable”,字段名是“MyID”,那么序列名将是“MyTable_MyID_seq”。
这个答案与@murugesanponappan的答案相同,但在他的解决方案中有一个语法错误。在alter命令中不能使用sub query (select max()…)所以要么你必须使用固定的数值,要么你需要使用变量来代替子查询。
如果您在加载自定义SQL数据进行初始化时看到此错误,另一种避免此错误的方法是:
而不是写:
INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10),
从初始数据中删除id(主键)
INSERT INTO book (name, price) VALUES ('Alchemist' , 10),
这使Postgres序列保持同步!