我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
重新启动所有序列到1使用:
-- Create Function
CREATE OR REPLACE FUNCTION "sy_restart_seq_to_1" (
relname TEXT
)
RETURNS "pg_catalog"."void" AS
$BODY$
DECLARE
BEGIN
EXECUTE 'ALTER SEQUENCE '||relname||' RESTART WITH 1;';
END;
$BODY$
LANGUAGE 'plpgsql';
-- Use Function
SELECT
relname
,sy_restart_seq_to_1(relname)
FROM pg_class
WHERE relkind = 'S';
其他回答
我的版本使用第一个,有一些错误检查…
BEGIN;
CREATE OR REPLACE FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text)
RETURNS pg_catalog.void AS
$BODY$
DECLARE
BEGIN
PERFORM 1
FROM information_schema.sequences
WHERE
sequence_schema = _table_schema AND
sequence_name = _sequence_name;
IF FOUND THEN
EXECUTE 'SELECT setval( ''' || _table_schema || '.' || _sequence_name || ''', ' || '(SELECT MAX(' || _columnname || ') FROM ' || _table_schema || '.' || _tablename || ')' || '+1)';
ELSE
RAISE WARNING 'SEQUENCE NOT UPDATED ON %.%', _tablename, _columnname;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql';
SELECT reset_sequence(table_schema, table_name, column_name, table_name || '_' || column_name || '_seq')
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';
DROP FUNCTION reset_sequence(_table_schema text, _tablename text, _columnname text, _sequence_name text) ;
COMMIT;
我建议在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
原文(也修复了序列所有权)在这里
选择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;';
克劳斯的答案是最有用的,除了有一点遗漏:你 必须在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”。
这里有一些非常硬核的答案,我假设它在被问到这个问题的时候是非常糟糕的,因为这里的很多答案在9.3版本中都不适用。8.0版本以后的文档提供了这个问题的答案:
SELECT setval('serial', max(id)) FROM distributors;
另外,如果你需要注意区分大小写的序列名,你可以这样做:
SELECT setval('"Serial"', max(id)) FROM distributors;