删除大部分大表后，重新启动现有行的主键号 [英] Restart primary key numbers of existing rows after deleting most of a big table

问题描述

我正在使用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:

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


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

1. Is there a way to get the index (id value) to start at 1 again?
2. 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.

这篇关于删除大部分大表后，重新启动现有行的主键号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！