如何重置 Postgres 主键序列何时不同步？

Question

我遇到了我的主键序列与表行不同步的问题。

也就是说，当我插入新行时，我收到重复键错误，因为串行数据类型中隐含的序列返回一个已经存在的数字。

这似乎是由于导入/恢复未正确维护序列引起的。

Answer 1

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

Answer 2

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) 为空，并且您也不能 setval 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 的用途有限，因为序列名称和重新启动值不能是表达式。

似乎最好的通用解决方案是使用 false 作为第三个参数调用 setval ，允许我们指定“下一个要使用的值”：

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

这勾选了我的所有框：

1. 避免对实际值进行硬编码序列名称
2. 处理空表正确
3. 处理具有现有数据的表，并且不留下
   序列中的漏洞

最后，请注意 pg_get_serial_sequence 仅当序列归列所有时才有效。 如果递增列被定义为 serial 类型，就会出现这种情况，但是，如果手动添加序列，则必须确保 ALTER SEQUENCE .. OWNED BY 也被定义执行。

即，如果使用 serial 类型创建表，则这应该全部有效：

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;

Answer 3

最短、最快的方式

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

tbl_id 是表 tbl 的 serial 或 IDENTITY 列，从序列 中绘制>tbl_tbl_id_seq（生成的默认名称）。 请参阅：

• 自动递增表格列

如果您不这样做不知道附加序列的名称（不必采用默认形式），请使用 pg_get_serial_sequence() （也适用于 IDENTITY）：

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

有这里没有差一错误。 手册：

双参数形式将序列的 last_value 字段设置为
指定值并将其 is_known 字段设置为 true，这意味着
next nextval 将在返回值之前推进序列。

大胆强调我的。

如果表格可以为空，并且在这种情况下实际上从 1 开始：

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id')
            , COALESCE(max(tbl_id) + 1, 1)
            , false)
FROM tbl;

我们不能只使用 2 参数形式并从 0 开始，因为下界默认情况下，序列数为 1（除非自定义）。

在并发写入负载下安全

为了同时防御并发序列活动或写入，请在 SHARE 模式下锁定表。 它可以防止并发事务写入更高的数字（或任何东西）。

还要考虑到客户端可能已经提前获取了序列号，而没有对主表进行任何锁定（在某些设置中可能会发生），仅增加序列的当前值，而不是减少它。 这可能看起来有些偏执，但这符合序列的本质和防范并发问题。

BEGIN;

LOCK TABLE tbl IN SHARE MODE;

SELECT setval('tbl_tbl_id_seq', max(tbl_id))
FROM   tbl
HAVING max(tbl_id) > (SELECT last_value FROM tbl_tbl_id_seq); -- prevent lower number

COMMIT;

SHARE 模式足以满足此目的。 手册：

此模式可保护表免受并发数据更改的影响。

它与 ROW EXCLUSIVE 模式冲突。

命令UPDATE、DELETE和INSERT在目标表上获取此锁定模式。

Answer 4

这将从公共重置所有序列，不对表或列名称做出任何假设。 在8.4版本上测试

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text) 
    RETURNS INTEGER AS 
    
    $body$  
      DECLARE 
      retval  INTEGER;
      BEGIN 
    
      EXECUTE 'SELECT setval( ''' || sequence_name  || ''', ' || '(SELECT MAX(' || columnname || 
          ') FROM "' || tablename || '")' || '+1)' INTO retval;
      RETURN retval;
      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%';

Answer 5

ALTER SEQUENCE 序列名 RESTART WITH (SELECT max(id) FROM table_name);
不起作用。

复制自 @tardate 答案：

SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;

Answer 6

在下面的示例中，表名称为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() 只会返回当前值，如文档所述 < a href="https://www.postgresql.org/docs/current/functions-sequence.html" rel="noreferrer">此处。

Answer 7

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

ALTER SEQUENCE "your_sequence_name" RESTART WITH 0;

您可以在零处放置要重新启动序列的任何数字。

默认序列名称为“TableName_FieldName_seq”。 例如，如果您的表名称为 "MyTable" 并且字段名称为 "MyID"，则您的序列名称将为 "MyTable_MyID_seq"< /代码>。

这个答案与 @murugesanponappan 的答案相同，但他的解决方案中存在语法错误。 您不能在 alter 命令中使用子查询 (select max()...) 。 因此，要么必须使用固定数值，要么需要使用变量来代替子查询。

Answer 8

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

Answer 9

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

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

原始文章（还修复了序列所有权）此处

Answer 10

当序列名称、列名称、表名称或模式名称具有有趣的字符（例如空格、标点符号等）时，这些函数就会充满危险。 我是这么写的：

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

使用不同的限定，您可以仅重置特定模式中的序列，依此类推。 例如，如果您想调整“公共”模式中的序列：

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 转换之外还有 ::text 转换。 我认为这是由于数据库是从旧的 PostgreSQL 版本中进行 pg_dump 造成的。 将会发生的是上面的函数sequence_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)

一旦您解决了这个问题，该函数就可以正确地对该表以及所有其他可能的表起作用使用相同的顺序。

Answer 11

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

Answer 12

从公共重置所有序列

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

Answer 13

当我使用实体框架创建数据库，然后使用初始数据为数据库播种时，会发生此问题，这会导致序列不匹配。

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

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$

Answer 14

这里有一些非常核心的答案，我假设在被问到这个问题时它曾经非常糟糕，因为这里的很多答案不适用于 9.3 版本。 自版本 8.0 以来的文档提供了这个问题的答案：

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

另外，如果您需要处理区分大小写的序列名称，可以这样做：

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

Answer 15

我的版本使用第一个，并进行一些错误检查......

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;

Answer 16

将它们放在一起

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 通常需要这样做）。

Answer 17

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

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;

Answer 18

在我还没有尝试过代码之前：在下面我发布
Klaus 和 user457226 解决方案的 sql 代码版本
它可以在我的电脑 [Postgres 8.3] 上运行，只需进行一些小的调整
对于 Klaus 版本和我的 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';

Answer 19

这个答案是毛罗的副本。

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

Answer 20

所以我可以看出这个帖子中没有足够的意见或重新发明的轮子，所以我决定让事情变得有趣。

下面是一个过程：

• 重点（仅影响）与表关联的序列
• 适用于 SERIAL 和 GENERATED AS IDENTITY 列
• 适用于 good_column_names 和“BAD_column_123”名称
• 如果表为空，则自动分配相应序列的定义起始值
• 允许仅影响特定序列（在 schema.table.column 表示法中）
• 具有预览模式

CREATE OR REPLACE PROCEDURE pg_temp.pg_reset_all_table_sequences(
    IN commit_mode BOOLEAN DEFAULT FALSE
,   IN mask_in TEXT DEFAULT NULL
) AS
$

/*
USAGE:

CALL pg_temp.pg_reset_all_table_sequences(<commit? FALSE | TRUE >, '<schema.table.column use % for wildcard>');

Examples:

CALL pg_temp.pg_reset_all_table_sequences(FALSE, 'your_schema.%'); -- PREVIEWS updates all sequences on all tables and columns in "your_schema"
CALL pg_temp.pg_reset_all_table_sequences(TRUE, 'your_schema.%'); -- COMMITS updates all sequences on all tables and columns in "your_schema"
CALL pg_temp.pg_reset_all_table_sequences(FALSE); -- PREVIEWS updates on all sequences in the entire database
CALL pg_temp.pg_reset_all_table_sequences(TRUE); -- COMMITS updates on all sequences in the entire database !!! probably not a good thing for a production env ¡¡¡

*/

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), TRUE) 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 ILIKE 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
;

Answer 21

如果您在加载自定义 SQL 数据进行初始化时看到此错误，避免这种情况的另一种方法是：

代替编写：

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

从初始数据中删除 id （主键）

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

这使 Postgres 序列保持同步！

Answer 22

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

Answer 23

select 'SELECT SETVAL(' || seq [ 1] || ', COALESCE(MAX('||column_name||')+1, 1) ) FROM '||table_name||';'
from (
       SELECT table_name, column_name, column_default, regexp_match(column_default, '''.*''') as seq
       from information_schema.columns
       where column_default ilike 'nextval%'
     ) as sequense_query

Answer 24

只需运行以下命令：

SELECT setval('my_table_seq', (SELECT max(id) FROM my_table));

Answer 25

克劳斯的回答是最有用的，除了一点点错过：你
必须在 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';

这是 user457226 解决方案的扩展，适用于以下情况
一些感兴趣的列名称不是“ID”。

Answer 26

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

Answer 27

更新架构中用作 ID 的所有序列的方法：

DO $ DECLARE
  r RECORD;
BEGIN
FOR r IN (SELECT tablename, pg_get_serial_sequence(tablename, 'id') as sequencename
          FROM pg_catalog.pg_tables
          WHERE schemaname='YOUR_SCHEMA'
          AND tablename IN (SELECT table_name 
                            FROM information_schema.columns 
                            WHERE table_name=tablename and column_name='id')
          order by tablename)
LOOP
EXECUTE
        'SELECT setval(''' || r.sequencename || ''', COALESCE(MAX(id), 1), MAX(id) IS NOT null)
         FROM ' || r.tablename || ';';
END LOOP;
END $;

Answer 28

上述答案都不适合我，因为我正在截断并重新插入单元测试的种子数据。

如果表的状态可以是空或有行，则需要使用以下内容：

SELECT COALESCE(MAX(id::INTEGER), 1),
        SETVAL(
            PG_GET_SERIAL_SEQUENCE('TABLE_NAME', 'id'),
            COALESCE(MAX(id::INTEGER), 1),
            COUNT(id::integer) > 0)
        FROM TABLE_NAME;

COUNT(id::integer) > 0是为了防止如果表为空则序列被设置为1。
你希望序列等于 0，这样当 postgres 内部调用 nextval() 时，它将返回 1，但你不能手动将 0 传递到序列函数中（令人困惑吧……）。

我假设您的 id 列不可为空。 如果它可为空，则计数可能无法按预期运行。

Answer 29

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

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 -

Answer 30

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