我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
试着重建索引。
更新:正如评论中指出的,这是对最初问题的回答。
其他回答
在下例中,表名为users,模式名为public(默认模式),请根据需要替换它。
1. 检查最大id:
SELECT MAX(id) FROM public.users;
2. 检查下一个值:
SELECT nextval('public."users_id_seq"');
3.如果下一个值小于最大id,重置它:
SELECT setval('public."users_id_seq"',
(SELECT MAX(id) FROM public.users)
);
注意:
Nextval()将在返回当前值之前递增序列,而currval()将只返回当前值,如本文所述。
试着重建索引。
更新:正如评论中指出的,这是对最初问题的回答。
重置所有序列,除了每个表的主键是“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%';
更新模式中用作ID的所有序列的方法:
DO $$ DECLARE
r RECORD;
BEGIN
FOR r IN (SELECT tablename, pg_get_serial_sequence(tablename, 'id') as sequencename
FROM pg_catalog.pg_tables
WHERE schemaname='YOUR_SCHEMA'
AND tablename IN (SELECT table_name
FROM information_schema.columns
WHERE table_name=tablename and column_name='id')
order by tablename)
LOOP
EXECUTE
'SELECT setval(''' || r.sequencename || ''', COALESCE(MAX(id), 1), MAX(id) IS NOT null)
FROM ' || r.tablename || ';';
END LOOP;
END $$;
还有另一个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将给出列表,而不是实际重置值