如何在postgres中重置序列并用新数据填充id列？

如果您不想保留 ID 的顺序，那么您可以

ALTER SEQUENCE seq RESTART WITH 1;
UPDATE t SET idcolumn=nextval('seq');

我怀疑有一种简单的方法可以按照您选择的顺序执行此操作，而无需重新创建整个表。

重置序列：

SELECT setval('sequence_name', 0);

更新当前记录：

UPDATE foo SET id = DEFAULT;

两者都提供的解决方案对我不起作用;

> SELECT setval('seq', 0);
ERROR:  setval: value 0 is out of bounds for sequence "seq" (1..9223372036854775807)

setval('seq', 1)以 2 开始编号，也以 2 开始编号，因为 seq.is_called 为 true（Postgres 版本 9.0.4）ALTER SEQUENCE seq START 1

对我有用的解决方案是：

> ALTER SEQUENCE seq RESTART WITH 1;
> UPDATE foo SET id = DEFAULT;

使用 PostgreSQL 8.4 或更高版本，无需再指定 。将使用记录者或上次设置的起始值（很可能是 1）。WITH 1CREATE SEQUENCEALTER SEQUENCE START WITH

重置序列：

ALTER SEQUENCE seq RESTART;

然后更新表的 ID 列：

UPDATE foo SET id = DEFAULT;

资料来源：PostgreSQL Docs

如果您使用的是 pgAdmin3，请展开“序列”，右键单击序列，转到“属性”，然后在“定义”选项卡中将“当前值”更改为您想要的任何值。无需查询。

仅供参考：如果需要在 ID 范围（例如 256 - 10000000）之间指定新的起始值：

SELECT setval('"Sequence_Name"', 
       (SELECT coalesce(MAX("ID"),255) 
           FROM "Table_Name" 
           WHERE "ID" < 10000000 and "ID" >= 256)+1
       ); 

要保留行的顺序，请执行以下操作：

UPDATE thetable SET rowid=col_serial FROM 
(SELECT rowid, row_number() OVER ( ORDER BY lngid) AS col_serial FROM thetable ORDER BY lngid) AS t1 
WHERE thetable.rowid=t1.rowid;

仅重置序列并更新所有行可能会导致重复的 ID 错误。在许多情况下，您必须更新所有行两次。首先使用更高的 ID 以避免重复，然后使用您实际想要的 ID。

请避免为所有 ID 添加固定金额（如其他评论中建议的那样）。如果行数超过此固定数量，会发生什么情况？假设序列的下一个值高于现有行的所有 ID（您只想填补空白），我会这样做：

UPDATE table SET id = DEFAULT;
ALTER SEQUENCE seq RESTART;
UPDATE table SET id = DEFAULT;

受到此处其他答案的启发，我创建了一个 SQL 函数来执行序列迁移。该函数将主键序列移动到新的连续序列，从现有序列范围内或外的任意值 （>= 1） 开始。

我在这里解释了我如何使用这个函数将两个具有相同架构但值不同的数据库迁移到一个数据库中。

首先，函数（它打印生成的 SQL 命令，使其 清楚实际发生了什么）：

CREATE OR REPLACE FUNCTION migrate_pkey_sequence
  ( arg_table      text
  , arg_column     text
  , arg_sequence   text
  , arg_next_value bigint  -- Must be >= 1
  )
RETURNS int AS $$
DECLARE
  result int;
  curr_value bigint = arg_next_value - 1;
  update_column1 text := format
    ( 'UPDATE %I SET %I = nextval(%L) + %s'
    , arg_table
    , arg_column
    , arg_sequence
    , curr_value
    );
  alter_sequence text := format
    ( 'ALTER SEQUENCE %I RESTART WITH %s'
    , arg_sequence
    , arg_next_value
    );
  update_column2 text := format
    ( 'UPDATE %I SET %I = DEFAULT'
    , arg_table
    , arg_column
    );
  select_max_column text := format
    ( 'SELECT coalesce(max(%I), %s) + 1 AS nextval FROM %I'
    , arg_column
    , curr_value
    , arg_table
    );
BEGIN
  -- Print the SQL command before executing it.
  RAISE INFO '%', update_column1;
  EXECUTE update_column1;
  RAISE INFO '%', alter_sequence;
  EXECUTE alter_sequence;
  RAISE INFO '%', update_column2;
  EXECUTE update_column2;
  EXECUTE select_max_column INTO result;
  RETURN result;
END $$ LANGUAGE plpgsql;

该函数采用以下参数：migrate_pkey_sequence

1. arg_table：表名（例如'example')
2. arg_column：主键列名（例如'id')
3. arg_sequence：序列名称（例如'example_id_seq')
4. arg_next_value：迁移后列的下一个值

它执行以下操作：

1. 将主键值移动到自由范围。我假设接下来是序列 与 1.这也处理了以下情况。nextval('example_id_seq')max(id)arg_next_value > max(id)
2. 将主键值移动到以 开头的连续范围。键值的顺序保持不变，但 范围不保留。arg_next_value
3. 打印序列中将遵循的下一个值。这在以下情况下很有用 您想要迁移另一个表的列并与此表合并。

为了演示，我们使用如下定义的序列和表（例如，使用）：psql

# CREATE SEQUENCE example_id_seq
  START WITH 1
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  CACHE 1;

# CREATE TABLE example
  ( id bigint NOT NULL DEFAULT nextval('example_id_seq'::regclass)
  );

然后，我们插入一些值（例如，从 3 开始）：

# ALTER SEQUENCE example_id_seq RESTART WITH 3;
# INSERT INTO example VALUES (DEFAULT), (DEFAULT), (DEFAULT);
-- id: 3, 4, 5

最后，我们将值迁移到以 1 开头。example.id

# SELECT migrate_pkey_sequence('example', 'id', 'example_id_seq', 1);
INFO:  00000: UPDATE example SET id = nextval('example_id_seq') + 0
INFO:  00000: ALTER SEQUENCE example_id_seq RESTART WITH 1
INFO:  00000: UPDATE example SET id = DEFAULT
 migrate_pkey_sequence
-----------------------
                     4
(1 row)

结果：

# SELECT * FROM example;
 id
----
  1
  2
  3
(3 rows)

只是为了简化和阐明 ALTER SEQUENCE 和 SELECT setval 的正确用法来重置序列：

ALTER SEQUENCE sequence_name RESTART WITH 1;

相当于

SELECT setval('sequence_name', 1, FALSE);

这些语句中的任何一个都可用于重置序列，您可以通过 nextval（'sequence_name'） 获取下一个值，如下所述：

nextval('sequence_name')

就我而言，我通过以下方式实现了这一点：

ALTER SEQUENCE table_tabl_id_seq RESTART WITH 6;

我的表被命名为 table 的位置

重置序列以从数字 1 开始的最佳方法是执行以下命令：

ALTER SEQUENCE <tablename>_<id>_seq RESTART WITH 1

因此，例如，对于 users 表，它将是：

ALTER SEQUENCE users_id_seq RESTART WITH 1

甚至自动增量列也不是 PK（在本例中称为 seq - aka sequence），您可以使用触发器实现：

如果存在，则删除表 devops_guide CASCADE;

SELECT 'create the "devops_guide" table'
;
   CREATE TABLE devops_guide (
      guid           UUID NOT NULL DEFAULT gen_random_uuid()
    , level          integer NULL
    , seq            integer NOT NULL DEFAULT 1
    , name           varchar (200) NOT NULL DEFAULT 'name ...'
    , description    text NULL
    , CONSTRAINT pk_devops_guide_guid PRIMARY KEY (guid)
    ) WITH (
      OIDS=FALSE
    );

-- START trg_devops_guide_set_all_seq
CREATE OR REPLACE FUNCTION fnc_devops_guide_set_all_seq()
    RETURNS TRIGGER
    AS $$
       BEGIN
         UPDATE devops_guide SET seq=col_serial FROM
         (SELECT guid, row_number() OVER ( ORDER BY seq) AS col_serial FROM devops_guide ORDER BY seq) AS tmp_devops_guide
         WHERE devops_guide.guid=tmp_devops_guide.guid;

         RETURN NEW;
       END;
    $$ LANGUAGE plpgsql;

 CREATE TRIGGER trg_devops_guide_set_all_seq
  AFTER UPDATE OR DELETE ON devops_guide
  FOR EACH ROW
  WHEN (pg_trigger_depth() < 1)
  EXECUTE PROCEDURE fnc_devops_guide_set_all_seq();

在我的情况下，导入错误的 sql 文件后，所有表中的序列都已损坏。 返回的值小于列的最大值。 因此，我创建了sql脚本来恢复每个表的所有序列：SELECT nextval('table_name_id_seq');id

DO
$$
DECLARE
   rec  record;
   table_seq text;
BEGIN
   FOR rec IN
      SELECT *
      FROM   pg_tables
      WHERE  tablename NOT LIKE 'pg\_%'
      ORDER  BY tablename
   LOOP
      table_seq := rec.tablename || '_id_seq';
     
      RAISE NOTICE '%', table_seq;
      EXECUTE format(E'SELECT setval(\'%I\', COALESCE((SELECT MAX(id)+1 FROM %I), 1), false);',
            table_seq, rec.tablename);
      
   END LOOP;
END
$$;

注意：如果任何表上都没有该列，则可以更新逻辑或根据上述逻辑单独处理它们。id

SELECT SETVAL('seq_my_table_pk_id', (SELECT MAX(my_table_pk_id) + 1 FROM my_table));

例如，若要使用表“TAB_B”的“FIELD_ID”字段的最大值更新序列“SEQ_A”的值，可以使用以下命令：

SELECT setval('SEQ_A', (SELECT max(FIELD_ID) FROM TAB_B));

此命令选择表“TAB_B”的“FIELD_ID”字段的最大值，并将其设置为序列“SEQ_A”的下一个值。