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

Question

我有一个包含超过一百万行的表。我需要重置序列并使用新值（1、2、3、4...等...）重新分配 id 列。有什么简单的方法可以做到这一点吗？

Answer 1

如果您不想保留 id 的顺序，那么我

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

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

Answer 2

对于 PostgreSQL 8.4 或更高版本，不再需要指定 WITH 1。将使用由 CREATE SEQUENCE 记录的起始值或由 ALTER SEQUENCE START WITH 最后设置的起始值（很可能为 1）。

重置序列：

ALTER SEQUENCE tablename_id_seq RESTART;

然后更新表的 ID 列：

UPDATE tablename SET id = DEFAULT;

来源：PostgreSQL Docs

Answer 3

重置顺序：

SELECT setval('sequence_name', 0);

更新当前记录：

UPDATE foo SET id = DEFAULT;

Answer 4

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

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

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

ALTER SEQUENCE users_id_seq RESTART WITH 1

Answer 5

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

ALTER SEQUENCE sequence_name RESTART WITH 1;

相当于

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

任一语句都可以用于重置序列，您可以通过 nextval('sequence_name') 获取下一个值，如此处< /a> 还有：

nextval('sequence_name')

Answer 6

两者提供的解决方案都不适合我；

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

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

对我有用的解决方案是：

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

Answer 7

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

Answer 8

要保留行的顺序：

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;

Answer 9

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

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 列，您可以更新逻辑或根据上面的逻辑。

Answer 10

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

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

Answer 11

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

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

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

Answer 12

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

ALTER SEQUENCE table_tabl_id_seq RESTART WITH 6;

我的表名为table

Answer 13

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

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

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

Answer 14

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

Answer 15

受到此处其他答案的启发，我创建了一个 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. 将主键值移动到空闲范围。我假设
   nextval('example_id_seq') 跟随 max(id) 并且序列开始
   与 1。这也处理 arg_next_value > 的情况。最大（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

最后，我们迁移 示例.id 值从 1 开始。

# 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)

Answer 16

即使自动增量列不是 PK（在本例中称为 seq - 又名序列），您也可以使用触发器来实现：

DROP TABLE IF EXISTS 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();