PostgreSQL删除除最旧记录之外的所有记录

概述我有一个PostgreSQL数据库,在多个设备名称上有多个objectid条目,但每个条目都有一个唯一的时间戳.该表看起来像这样： address | devicename | objectid | timestamp --------+------------+---------------+------------------------------1.1.1.1 我有一个Postgresql数据库,在多个设备名称上有多个objectID条目,但每个条目都有一个唯一的时间戳.该表看起来像这样：

address | devicename | objectID      |  timestamp       --------+------------+---------------+------------------------------1.1.1.1 | device1    | vs_hub.ch1_25 | 2012-10-02 17:36:41.011629+001.1.1.2 | device2    | vs_hub.ch1_25 | 2012-10-02 17:48:01.755559+001.1.1.1 | device1    | vs_hub.ch1_25 | 2012-10-03 15:37:09.06065+001.1.1.2 | device2    | vs_hub.ch1_25 | 2012-10-03 15:48:33.93128+001.1.1.1 | device1    | vs_hub.ch1_25 | 2012-10-05 16:01:59.266779+001.1.1.2 | device2    | vs_hub.ch1_25 | 2012-10-05 16:13:46.843113+001.1.1.1 | device1    | vs_hub.ch1_25 | 2012-10-06 01:11:45.853361+001.1.1.2 | device2    | vs_hub.ch1_25 | 2012-10-06 01:23:21.204324+00

我想删除除odjectID和devicename之外的所有条目.在这种情况下,我想删除所有,但：

1.1.1.1 | device1 | vs_hub.ch1_25 | 2012-10-02 17:36:41.011629+001.1.1.2 | device2 | vs_hub.ch1_25 | 2012-10-02 17:48:01.755559+00

有办法吗？或者是否可以将“objectID和devicename”中最旧的条目选择到临时表中？

解决方法 为了提炼所描述的结果,这可能是最简单和最快的：

SELECT disTINCT ON (devicename,objectID) *FROM   tblORDER  BY devicename,objectID,ts DESC;

细节和解释in this related answer.

从您的示例数据中,我得出结论,您将删除原始表的大部分内容.它可能更快到TRUNCATE表(或者DROP& recreate,因为你应该添加一个代理pk列)并将剩余的行写入其中.这也将为您提供一个prestine表,以最适合您的查询的方式隐式聚类(排序),并保存VACUUM必须要做的工作.它总体上可能更快：

我还强烈建议在您的表中添加一个代理主键,最好是serial列.

BEGIN;CREATE TEMP table tmp_tbl ON COMMIT DROP ASSELECT disTINCT ON (devicename,ts DESC;TruncATE tbl;ALTER table tbl ADD column tbl_ID serial PRIMARY KEY;-- or,if you can afford to drop & recreate:-- DROP table tbl;-- CREATE table tbl (--   tbl_ID serial PRIMARY KEY--,address text--,devicename text--,objectID text--,ts timestamp);INSERT INTO tbl (address,devicename,ts)SELECT address,tsFROM   tmp_tbl;COMMIT;

在交易中完成所有 *** 作,以确保您不会在中途失败.

只要您的temp_buffers设置足以容纳临时表,这就很快.否则系统将开始将数据交换到磁盘,性能会下降.您可以为当前会话设置temp_buffers,如下所示：

SET temp_buffers = 1000MB;

所以你不要浪费你通常不需要temp_buffers的RAM.必须在会话中第一次使用临时对象之前.更多信息,请参见this related answer.

此外,由于INSERT在事务中遵循TruncATE,因此在Write Ahead Log上将很容易 – 提高性能.

考虑使用CREATE table AS替代路由：

> What causes large INSERT to slow down and disk usage to explode?

唯一的缺点：你需要在桌子上独家锁定.这可能是具有大量并发负载的数据库中的问题.

最后,永远不要使用时间戳作为列名.它是每个sql标准中的reserved word和Postgresql中的类型名称.您可能已经注意到我将该列重命名为ts.

总结

以上是内存溢出为你收集整理的PostgreSQL删除除最旧记录之外的所有记录全部内容，希望文章能够帮你解决PostgreSQL删除除最旧记录之外的所有记录所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。