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

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

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

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$

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

这个答案是从毛罗那里抄来的。

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

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

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;

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;

这符合我所有的条件:

避免硬编码实际的序列名 正确处理空表 使用现有数据处理表，并且不会留下 序列中的漏洞

最后，请注意，pg_get_serial_sequence仅在序列属于列时才有效。如果将递增列定义为串行类型，则会出现这种情况，但是如果手动添加序列，则需要确保ALTER sequence。“OWNED BY”也被执行。

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

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;