删除大部分大表后，重新启动现有行的主键号

本文介绍了删除大部分大表后，重新启动现有行的主键号的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用PostgreSQL 8.4.13数据库。 最近我在一个表中大约有8650万条记录。我删除了几乎所有记录-现在只剩下5000条记录。我跑了：

I am working with a PostgreSQL 8.4.13 database. Recently I had around around 86.5 million records in a table. I deleted almost all of them - only 5000 records are left now. I ran:

vacuum full

在删除行并将磁盘空间返回给操作系统后（）

after deleting the rows and that returned disk space to the OS (thx to suggestion from fellow SO member)

但是我看到我的身份证号码仍然停留在数百万。例如：

But I see that my id numbers are still stuck at millions. For ex:

id | date_time | event_id | secs_since_1970 | value ---------+-------------------------+----------+-----------------+----------- 61216287 | 2013/03/18 16:42:42:041 | 6 | 1363646562.04 | 46.4082 61216289 | 2013/03/18 16:42:43:041 | 6 | 1363646563.04 | 55.4496 61216290 | 2013/03/18 16:42:44:041 | 6 | 1363646564.04 | 40.0553 61216291 | 2013/03/18 16:42:45:041 | 6 | 1363646565.04 | 38.5694

试图启动 id 其余行的 1 值，我尝试过：

In an attempt to start the id value at 1 again for the remaining rows, I tried:

cluster mytable_pkey on mytable;

其中 mytable 是表的名称。但这没有帮助。 所以，我的问题是：

where mytable is the name of my table. But that did not help. So, my question(s) is/are:

有没有办法获取索引（id值）到再次从1开始？

如果我添加或更新带有新记录的表，它是从1开始还是选择下一个最高的整数值（例如上例中的61216292）？ / li>

Is there a way to get the index (id value) to start at 1 again?

If I add or update the table with a new record, will it start from 1 or pick up the next highest integer value (say 61216292 in above example)?

我的表描述如下：没有FK约束，也没有序列。

My table description is as follows: There is no FK constraint and no sequence in it.

jbossql=> \d mytable; Table "public.mytable" Column | Type | Modifiers -----------------+------------------------+----------- id | bigint | not null date_time | character varying(255) | event_id | bigint | secs_since_1970 | double precision | value | real | Indexes: "mydata_pkey" PRIMARY KEY, btree (id) CLUSTER

推荐答案

删除主键字段并创建一个临时序列。

Drop the primary key fisrt and create a temporary sequence.

alter table mytable drop constraint mydata_pkey; create temporary sequence temp_seq;

使用序列更新：

update mytable set id = nextval('temp_seq');

重新创建主键并删除序列

Recreate the primary key and drop the sequence

alter table mytable add primary key (id); drop sequence temp_seq;

如果此表上有外键依赖项，则必须先处理它，然后再处理更新将是一个更复杂的过程。

If there is a foreign key dependency on this table then you will have to deal with it first and the update will be a more complex procedure.