如何重置序列中的 postgres 和填写新数据的 id 列？

我有一个超过百万行的表。我需要重置序列和重新分配 id 列与新的值(1,2,3,4... 等等。.).有什么简单的方法吗？

325887 次浏览
小开
最佳答案

如果您不想保留 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开始编号，ALTER SEQUENCE seq START 1也以2开始编号，因为 seq.is _ called 是 true (Postgres 版本9.0.4)

对我有效的解决办法是:

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

对于 PostgreSQL 8.4或更新的版本，不再需要指定 WITH 1。将使用 CREATE SEQUENCE记录的起始值或 ALTER SEQUENCE START WITH最后设置的起始值(最有可能的是1)。

重置序列:

ALTER SEQUENCE seq RESTART;

然后更新表的 ID 列:

UPDATE foo SET id = DEFAULT;

资料来源: PostgreSQL 文档

小开

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

小开

FYI: 如果需要在 ID 范围(例如256-10000000)之间指定一个新的 start 值:

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. 序列名称(例如 'example_id_seq')
  4. arg_next_value: 迁移后列的下一个值

它执行下列业务:

  1. 将主键值移动到自由范围 nextval('example_id_seq')跟随 max(id)并且序列开始 这也处理了 arg_next_value > max(id)。
  2. 将主键值移动到从 键值的顺序被保留，但 范围没有被保留。
  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

最后，我们将 example.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)
小开

只是为了简化和澄清 更改序列和 选择 setval重置序列的正确用法:

ALTER SEQUENCE sequence_name RESTART WITH 1;

相当于

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

这两个语句中的任何一个都可以用来重置序列，并且您可以通过 nextval (‘ order _ name’)获得下一个值，如 给你所述:

nextval('sequence_name')
小开

就我而言，我是通过以下方式实现这一目标的:

ALTER SEQUENCE table_tabl_id_seq RESTART WITH 6;

我的桌子叫 桌子

小开

重置序列以1开始的最好方法是执行以下操作:

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

例如，对于用户表，它是:

ALTER SEQUENCE users_id_seq RESTART WITH 1
小开

甚至自动递增列也不是 PK (在本例中它被称为 seq-aka 序列) ，您可以通过触发器实现这一点:

如果存在，删除表;

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 文件后，所有表中的序列都已损坏。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列，那么您应该更新逻辑或者根据上面的逻辑分别处理它们。

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

提交答案