如何重置postgres&# 39;不同步时的主键序列?

我遇到了一个问题，我的主键序列与我的表行不同步。

也就是说，当我插入一个新行时，我得到一个重复的键错误，因为在串行数据类型中隐含的序列返回一个已经存在的数字。

这似乎是由于导入/恢复没有正确地维护序列造成的。

392276 次浏览
小开

# EYZ0试试。

更新:正如评论中指出的，这是对最初问题的回答。

小开
最佳答案 
-- Login to psql and run the following


-- What is the result?
SELECT MAX(id) FROM your_table;


-- Then run...
-- This should be higher than the last result.
SELECT nextval('your_table_id_seq');


-- If it's not higher... run this set the sequence last to your highest id.
-- (wise to run a quick pg_dump first...)


BEGIN;
-- protect against concurrent inserts while you update the counter
LOCK TABLE your_table IN EXCLUSIVE MODE;
-- Update the sequence
SELECT setval('your_table_id_seq', COALESCE((SELECT MAX(id)+1 FROM your_table), 1), false);
COMMIT;

源代码- Ruby论坛

小开

ALTER SEQUENCE sequence_name RESTART WITH (SELECT max(id) FROM table_name); 不工作。< / p >

复制自@tardate答案:

SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;
小开

pg_get_serial_sequence可以用来避免任何关于序列名的错误假设。这将在一个镜头中重置序列:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1);

或者更简洁地说:

SELECT pg_catalog.setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

然而，这个表单不能正确地处理空表，因为max(id)是空的，你也不能设置val 0，因为它超出了序列的范围。一个解决方法是使用ALTER SEQUENCE语法。

ALTER SEQUENCE table_name_id_seq RESTART WITH 1;
ALTER SEQUENCE table_name_id_seq RESTART; -- 8.4 or higher

但是ALTER SEQUENCE的作用有限，因为序列名和重启值不能是表达式。

似乎最好的万能解决方案是调用setval，并将false作为第三个参数，允许我们指定“下一个要使用的值”:

SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

这符合我所有的条件:

  1. 避免硬编码实际的序列名
  2. 正确处理空表
  3. 处理具有现有数据的表，并且不留下 序列中的洞

最后，请注意，只有当序列属于列时，pg_get_serial_sequence才有效。如果递增列被定义为serial类型，则会出现这种情况，但是如果序列是手动添加的，则有必要确保也执行ALTER SEQUENCE .. OWNED BY。

例如，如果使用serial类型创建表，这应该都可以工作:

CREATE TABLE t1 (
id serial,
name varchar(20)
);


SELECT pg_get_serial_sequence('t1', 'id'); -- returns 't1_id_seq'


-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1;

但如果手动添加序列:

CREATE TABLE t2 (
id integer NOT NULL,
name varchar(20)
);


CREATE SEQUENCE t2_custom_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;


ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval('t2_custom_id_seq'::regclass);


ALTER SEQUENCE t2_custom_id_seq OWNED BY t2.id; -- required for pg_get_serial_sequence


SELECT pg_get_serial_sequence('t2', 'id'); -- returns 't2_custom_id_seq'


-- reset the sequence, regardless whether table has rows or not:
SELECT setval(pg_get_serial_sequence('t2', 'id'), coalesce(max(id),0) + 1, false) FROM t1;
小开

重置所有公共序列

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text) RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE 'SELECT setval( '''
|| tablename
|| '_id_seq'', '
|| '(SELECT id + 1 FROM "'
|| tablename
|| '" ORDER BY id DESC LIMIT 1), false)';
END;
$body$  LANGUAGE 'plpgsql';


select sequence_name, reset_sequence(split_part(sequence_name, '_id_seq',1)) from information_schema.sequences
where sequence_schema='public';
小开

这将重置所有来自public的序列，不假设表或列名。在8.4版上测试

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text)
RETURNS "pg_catalog"."void" AS
    

$body$
DECLARE
BEGIN
    

EXECUTE 'SELECT setval( ''' || sequence_name  || ''', ' || '(SELECT MAX(' || columnname ||
') FROM ' || tablename || ')' || '+1)';
    

END;
    

$body$  LANGUAGE 'plpgsql';
    

    

SELECT table_name || '_' || column_name || '_seq',
reset_sequence(table_name, column_name, table_name || '_' || column_name || '_seq')
FROM information_schema.columns where column_default like 'nextval%';
小开
克劳斯的回答是最有用的，除了有一点遗漏:你

然而，如果你确定没有表+列名是等价的 对于两个不同的表，你也可以使用:

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解决方案的扩展

.

.
小开

之前我还没有尝试过的代码:在下面我张贴 Klaus和user457226解决方案的sql代码版本 它在我的电脑上运行(Postgres 8.3)，只做了一些小调整 我的版本是user457226。

克劳斯解决方案:

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
constraint_column_usage.table_name as tablename,
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
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 ||' restart  with ' || c;
END LOOP;


RETURN 1; END;
$body$ LANGUAGE plpgsql;


select rebuilt_sequences();

User457226解决方案:

--drop function IF EXISTS reset_sequence (text,text) RESTRICT;
CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text,columnname text) RETURNS bigint --"pg_catalog"."void"
AS
$body$
DECLARE seqname character varying;
c integer;
BEGIN
select tablename || '_' || columnname || '_seq' into seqname;
EXECUTE 'SELECT max("' || columnname || '") FROM "' || tablename || '"' into c;
if c is null then c = 0; end if;
c = c+1; --because of substitution of setval with "alter sequence"
--EXECUTE 'SELECT setval( "' || seqname || '", ' || cast(c as character varying) || ', false)'; DOES NOT WORK!!!
EXECUTE 'alter sequence ' || seqname ||' restart with ' || cast(c as character varying);
RETURN nextval(seqname)-1;
END;
$body$ LANGUAGE 'plpgsql';


select sequence_name, PG_CLASS.relname, PG_ATTRIBUTE.attname,
reset_sequence(PG_CLASS.relname,PG_ATTRIBUTE.attname)
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 || '_seq'
where sequence_schema='public';
小开

当序列名、列名、表名或模式名中有空格、标点符号等有趣的字符时，这些函数充满了危险。我这样写:

CREATE OR REPLACE FUNCTION sequence_max_value(oid) RETURNS bigint
VOLATILE STRICT LANGUAGE plpgsql AS  $$
DECLARE
tabrelid oid;
colname name;
r record;
newmax bigint;
BEGIN
FOR tabrelid, colname IN SELECT attrelid, attname
FROM pg_attribute
WHERE (attrelid, attnum) IN (
SELECT adrelid::regclass,adnum
FROM pg_attrdef
WHERE oid IN (SELECT objid
FROM pg_depend
WHERE refobjid = $1
AND classid = 'pg_attrdef'::regclass
)
) LOOP
FOR r IN EXECUTE 'SELECT max(' || quote_ident(colname) || ') FROM ' || tabrelid::regclass LOOP
IF newmax IS NULL OR r.max > newmax THEN
newmax := r.max;
END IF;
END LOOP;
END LOOP;
RETURN newmax;
END; $$ ;

你可以为单个序列调用它，通过传递OID，它将返回任何表所使用的默认序列的最大值;或者你可以用这样的查询来运行它，重置数据库中的所有序列:

 select relname, setval(oid, sequence_max_value(oid))
from pg_class
where relkind = 'S';

使用不同的qual，只能重置某个模式中的序列，等等。例如，如果你想调整"public"模式中的序列:

select relname, setval(pg_class.oid, sequence_max_value(pg_class.oid))
from pg_class, pg_namespace
where pg_class.relnamespace = pg_namespace.oid and
nspname = 'public' and
relkind = 'S';

注意，由于setval()的工作方式，您不需要向结果添加1。

作为结束语，我必须警告，一些数据库似乎有默认的序列链接方式，不让系统目录拥有它们的完整信息。当你在psql的\d中看到这样的东西时，就会发生这种情况:

alvherre=# \d baz
Tabla «public.baz»
Columna |  Tipo   |                 Modificadores
---------+---------+------------------------------------------------
a       | integer | default nextval(('foo_a_seq'::text)::regclass)

请注意，该默认子句中的nextval()调用除了::regclass强制转换外，还有一个::text强制转换。这是因为数据库是从旧的PostgreSQL版本中pg_dump的。上面的sequence_max_value()函数将忽略这样一个表。为了解决这个问题，你可以重新定义DEFAULT子句，以直接引用序列而不进行强制转换:

alvherre=# alter table baz alter a set default nextval('foo_a_seq');
ALTER TABLE

然后psql会正确显示它:

alvherre=# \d baz
Tabla «public.baz»
Columna |  Tipo   |             Modificadores
---------+---------+----------------------------------------
a       | integer | default nextval('foo_a_seq'::regclass)

一旦您修复了这个问题，这个函数就可以正确地工作于这个表以及所有其他可能使用相同序列的表。

小开

我的版本使用第一个，有一些错误检查…

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;
小开

把它们放在一起

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text)
RETURNS "pg_catalog"."void" AS
$body$
DECLARE
BEGIN
EXECUTE 'SELECT setval( pg_get_serial_sequence(''' || tablename || ''', ''id''),
(SELECT COALESCE(MAX(id)+1,1) FROM ' || tablename || '), false)';
END;
$body$  LANGUAGE 'plpgsql';

将修复给定表的'id'序列(对于django来说通常是必要的)。

小开

重置所有序列，除了每个表的主键是“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%';
小开

丑陋的黑客修复它使用一些贝壳魔法，不是一个伟大的解决方案，但可能会激励其他人有类似的问题:)

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 -
小开

最快捷的方式

SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;

tbl_id是表 tbl的 serial或 IDENTITY列，从序列 tbl_tbl_id_seq(得到的默认名称)中绘制。参见:

如果 您不知道附加序列的名称(不一定是默认形式) ，使用 强 > pg_get_serial_sequence()强(也适用于 IDENTITY) :

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;

这里没有差一错误

双参数窗体将序列的 last_value字段设置为 指定的值并将其 is_called字段设置为 true，这意味着 接下来的 nextval将在返回值之前将序列 前移。

强调我的。

如果 表可以是空的，在这种情况下实际上是从1开始:

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id')
, COALESCE(max(tbl_id) + 1, 1)
, false)
FROM tbl;

我们不能只使用2参数形式并从 0开始，因为序列的下限默认为 强(除非定制)。

在并发写入负载下的安全性

为了防止并发序列活动或写操作，锁定表采用 SHARE模式。它可以防止并发事务写入更高的数字(或任何数字)。

还要考虑到客户端可能已经提前获取了序列号，而没有在主表上设置任何锁，然而(可能在某些设置中发生) ，只有 增加序列的当前值，永远不要减少它。这可能看起来有些偏执，但是这符合序列的本质，并且可以防止并发问题。

BEGIN;


LOCK TABLE tbl IN SHARE MODE;


SELECT setval('tbl_tbl_id_seq', max(tbl_id))
FROM   tbl
HAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq); -- prevent lower number


COMMIT;

模式是足够强大的目的

此模式保护表不受并发数据更改的影响。

它与 ROW EXCLUSIVE模式冲突。

命令 UPDATE、 DELETE和 INSERT在目标表上获取这种锁模式。

小开

如果在加载用于初始化的自定义 SQL 数据时看到此错误，另一种避免此错误的方法是:

而不是写:

INSERT INTO book (id, name, price) VALUES (1 , 'Alchemist' , 10),

从初始数据中删除 id(主键)

INSERT INTO book (name, price) VALUES ('Alchemist' , 10),

这使得 Postgres 序列保持同步！

小开

这个命令只用于更改 postgreql 中自动生成的键序列值

ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;

你可以用任意数字代替零来重新启动序列。

默认序列名称将 "TableName_FieldName_seq"。例如，如果表名为 "MyTable"，字段名为 "MyID"，则序列名为 "MyTable_MyID_seq"。

这个答案和@murugesanponappan 的答案是一样的，但是在他的解决方案中有一个语法错误。在 alter命令中不能使用子查询 (select max()...)。因此，要么必须使用固定的数值，要么必须使用变量代替子查询。

小开

这里有一些非常核心的答案，我假设在被问到这个问题的时候，它曾经非常糟糕，因为从这里得到的很多答案在9.3版本中都不起作用。自8.0版以来的 文件为这个问题提供了答案:

SELECT setval('serial', max(id)) FROM distributors;

另外，如果您需要处理区分大小写的序列名，那么您可以这样做:

SELECT setval('"Serial"', max(id)) FROM distributors;
小开

SELECT 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;';
小开

重新检查公共模式函数中的所有序列

CREATE OR REPLACE FUNCTION public.recheck_sequence (
)
RETURNS void AS
$body$
DECLARE
_table_name VARCHAR;
_column_name VARCHAR;
_sequence_name VARCHAR;
BEGIN
FOR _table_name IN SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public' LOOP
FOR _column_name IN SELECT column_name FROM information_schema.columns WHERE table_name = _table_name LOOP
SELECT pg_get_serial_sequence(_table_name, _column_name) INTO _sequence_name;
IF _sequence_name IS NOT NULL THEN
EXECUTE 'SELECT setval('''||_sequence_name||''', COALESCE((SELECT MAX('||quote_ident(_column_name)||')+1 FROM '||quote_ident(_table_name)||'), 1), FALSE);';
END IF;
END LOOP;
END LOOP;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
小开

这是 Mauro 的复印件。

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();
小开

我建议这个解决方案可以在 postgres 维基上找到。它更新所有的表序列。

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

原始文章(也有序列所有权的修正) 给你

小开

要重新启动所有序列，请使用:

-- 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';
小开

另一个 plpgsql-仅在 max(att) > then 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将给出列表，而不是实际重置值

小开

我花了一个小时的时间尝试用数据库使用混合表格和列来获得 djSnowsill 的答案，最后由于 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表示引号

小开

这个问题发生在我使用实体框架创建数据库，然后种子数据库与初始数据，这使得序列不匹配。

我通过创建一个脚本来解决这个问题，这个脚本是在建立数据库之后运行的:

DO
$do$
DECLARE tablename text;
BEGIN
-- change the where statments to include or exclude whatever tables you need
FOR tablename IN SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name != '__EFMigrationsHistory'
LOOP
EXECUTE format('SELECT setval(pg_get_serial_sequence(''"%s"'', ''Id''), (SELECT MAX("Id") + 1 from "%s"))', tablename, tablename);
END LOOP;
END
$do$
小开 
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
小开

更新模式中用作 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 $$;
小开

只要按下面的命令运行:

SELECT setval('my_table_seq', (SELECT max(id) FROM my_table));
小开

这里有很多好的答案。我有同样的需要后，重新加载我的 Django 数据库。

但我需要:

  • 都在一个函数中
  • 可以一次修复一个或多个模式
  • 可以修好所有的桌子，或者一次只修一张桌子
  • 也想要一个很好的方式，看到究竟是什么改变了，或没有改变

这似乎与原来的要求非常相似。
多亏了 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
小开

因此，我可以告诉有没有足够的意见或重新发明的轮子在这个线程，所以我决定增加情趣。

下面是一个程序:

  • 集中(仅影响)与表相关联的序列
  • 同时适用于 SERIAL 列和 GENERATED AS IDENTITY 列
  • 适用于 good _ column _ name 和“ 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
;

预览:

call pg_reset_all_table_sequences();

承诺:

call pg_reset_all_table_sequences(true);

只指定目标表:

call pg_reset_all_table_sequences('schema.table.column');

小开

在下面的示例中，桌子名称为 users，模式名称为 public(默认模式) ，根据需要替换它。

1. 检查 max id:

SELECT MAX(id) FROM public.users;

2. 检查 next value:

SELECT nextval('public."users_id_seq"');

3. 如果 next value低于 max id，重新设置:

SELECT setval('public."users_id_seq"',
(SELECT MAX(id) FROM public.users)
);

注:

nextval()将在返回当前值之前递增序列，而 currval()将只返回当前值，如文档 给你所示。

小开

我无法找到 Rails 的明确答案。

来自 rails console

ActiveRecord::Base.connection.execute("SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;")

例如，用 users代替 table_name。

小开 
SELECT setval('sequencename', COALESCE((SELECT MAX(id)+1 FROM tablename), 1), false);

提交答案