我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
我遇到了一个问题,我的主键序列与我的表行不同步。
也就是说,当我插入一个新行时,我得到一个重复的键错误,因为在串行数据类型中隐含的序列返回一个已经存在的数字。
这似乎是由于导入/恢复没有正确地维护序列造成的。
当前回答
更新模式中用作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 $$;
其他回答
所以我可以告诉你,在这个帖子里没有足够的意见或重新发明的轮子,所以我决定让事情变得有趣。
下面是一个程序:
关注(仅影响)与表相关的序列 适用于串行和生成的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”);
克劳斯的答案是最有用的,除了有一点遗漏:你 必须在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”。
我的版本使用第一个,有一些错误检查…
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;
select 'SELECT SETVAL(' || seq [ 1] || ', COALESCE(MAX('||column_name||')+1, 1) ) FROM '||table_name||';'
from (
SELECT table_name, column_name, column_default, regexp_match(column_default, '''.*''') as seq
from information_schema.columns
where column_default ilike 'nextval%'
) as sequense_query
丑陋的黑客修复它使用一些贝壳魔法,不是一个伟大的解决方案,但可能会激励其他人有类似的问题:)
pg_dump -s <DATABASE> | grep 'CREATE TABLE' | awk '{print "SELECT setval(#" $3 "_id_seq#, (SELECT MAX(id) FROM " $3 "));"}' | sed "s/#/'/g" | psql <DATABASE> -f -