当Postgres的主键序列不同步时如何重置它?

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

Source - Ruby Forum

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

但是 修改序列 由于序列名和重新启动值不能是表达式,因此使用受限。

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

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

这会勾选我所有的框:

1. 避免对实际序列名进行硬编码
2. 正确处理空表
3. 处理具有现有数据的表,并且不保留 序列中的孔

最后,请注意 pg_获取_序列号 仅当序列属于列时才有效。如果递增列被定义为 serial 但是,如果手动添加序列,则必须确保 ALTER SEQUENCE .. OWNED BY 也执行。

即如果 系列 类型用于创建表,这应该可以全部工作:

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;

这个 最短最快 方式:

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

tbl_id 作为 serial 表列 tbl ,从序列中绘制 tbl_tbl_id_seq (默认自动名称)。

如果您不知道附加序列的名称(不必使用默认格式),请使用 pg_get_serial_sequence() :

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

这里没有一个错误。 Per documentation:

双参数窗体将序列的最后一个值字段设置为 指定值并设置其 is_called 字段为真,表示 下一个 nextval 将推进序列 返回值之前。

大胆强调我的。

并发性

在上述查询中,还没有针对并发序列活动或对表进行写操作的防御。如果相关的话,你可以 锁桌子 在独占模式下。它可以防止并发事务在您尝试同步时写入更高的数字。(它还临时阻止无害的写入,而不会干扰最大数目。)

但是它并没有考虑到那些在主表上没有任何锁的情况下提前获取序列号的客户机(可能会发生这种情况)。考虑到这一点,只有 增加 序列的当前值,永远不要减小它。它可能看起来有点偏执,但这与序列的性质一致,并且可以抵御并发性问题。

BEGIN;

LOCK TABLE tbl IN EXCLUSIVE MODE;

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

COMMIT;

这将从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%';

用(从表名称中选择max(id))改变序列序列名称重新启动; 不起作用。

从@tardate answer复制:

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的答案相同,但他的解决方案中存在语法错误。不能使用子查询 (select max()...) 在里面 alter 命令。因此,要么必须使用固定的数值,要么需要使用变量来代替子查询。

重置所有序列,除每个表的主键为“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%';

当序列名、列名、表名或模式名具有有趣的字符(如空格、标点符号等)时,这些函数充满了危险。我写的是:

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

使用不同的限定符,您只能重置特定模式中的序列,依此类推。例如,如果要调整“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 cast之外还有一个::text cast。我 认为 这是因为数据库是从旧的PostgreSQL版本中转储的。会发生的是,上面的函数序列_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)

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

从公共位置重置所有序列

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

这里有一些真正的核心答案,我假设在被问到这个问题的时候它曾经是非常糟糕的,因为这里的许多答案在9.3版中不起作用。这个 documentation 因为8.0版提供了这个问题的答案:

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

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

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

我的版本使用第一个版本,并进行了一些错误检查…

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通常需要)。

我建议在Postgreswiki上找到这个解决方案。它会更新表的所有序列。

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

原始物品(也带有序列所有权固定) here

还有另一个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 将给出列表,而不是实际重置值

在我还没有尝试代码之前:在下面我发布 Klaus和User457226解决方案的SQL代码版本 它在我的电脑上工作[Postgres8.3],只有一些小附件 对于克劳斯一号和我的版本,对于用户457226一号。

克劳斯解决方案:

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

用户457226解决方案:

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

当使用实体框架创建数据库,然后用初始数据为数据库种子时,我会遇到这个问题,这会使序列不匹配。

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

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$

要将所有序列重新启动为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';

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

不是写:

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

移除 id (主键)来自初始数据

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

这使Postgres序列保持同步!

这个答案是毛罗的副本。

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对使用混合大小写表和列的数据库的回答,最后在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 引文手段

克劳斯的回答是最有用的,对一个小小的小姐来说是:你 必须在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';

这是用户457226解决方案在以下情况下的扩展: 某些感兴趣的列名不是“id”。

丑陋的黑客使用一些外壳魔法来修复它,这不是一个很好的解决方案,但可能会激发其他人的类似问题:)

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 -

尝试 reindex .

更新:正如评论中指出的,这是对原始问题的答复。

SELECT setval... 制作JDBC-BORK,所以这里有一个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;';