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

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;

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

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;

在下例中，表名为users，模式名为public(默认模式)，请根据需要替换它。

1. 检查最大id:

SELECT MAX(id) FROM public.users;

2. 检查下一个值:

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

3.如果下一个值小于最大id，重置它:

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

注意:

Nextval()将在返回当前值之前递增序列，而currval()将只返回当前值，如本文所述。

此命令仅用于更改postgresql中自动生成的键序列值

ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;

在零的地方，你可以放任何你想重新启动序列的数字。

默认序列名为“TableName_FieldName_seq”。例如，如果您的表名是“MyTable”，字段名是“MyID”，那么序列名将是“MyTable_MyID_seq”。

这个答案与@murugesanponappan的答案相同，但在他的解决方案中有一个语法错误。在alter命令中不能使用sub query (select max()…)所以要么你必须使用固定的数值，要么你需要使用变量来代替子查询。

我花了一个小时试图让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表示在引号中

之前我还没有尝试过代码:在下面我张贴 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';