我花了一个小时试图让djsnowsill的答案与数据库使用Mixed Case表和列一起工作，然后终于偶然发现了解决方案，感谢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表示在引号中

所以我可以告诉你，在这个帖子里没有足够的意见或重新发明的轮子，所以我决定让事情变得有趣。

下面是一个程序:

关注(仅影响)与表相关的序列 适用于串行和生成的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”);

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

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

重置所有公共序列

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

我花了一个小时试图让djsnowsill的答案与数据库使用Mixed Case表和列一起工作，然后终于偶然发现了解决方案，感谢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表示在引号中