-- 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论坛

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

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;

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

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

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

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

在下例中，表名为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()将只返回当前值，如本文所述。