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

小编典典

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

sql

我有一个PostgreSQL数据库，其中有多个条目，在objectid上devicenames，但timestamp每个条目都有一个唯一的。该表如下所示：

address | devicename | objectid      |  timestamp       
--------+------------+---------------+------------------------------
1.1.1.1 | device1    | vs_hub.ch1_25 | 2012-10-02 17:36:41.011629+00
1.1.1.2 | device2    | vs_hub.ch1_25 | 2012-10-02 17:48:01.755559+00
1.1.1.1 | device1    | vs_hub.ch1_25 | 2012-10-03 15:37:09.06065+00
1.1.1.2 | device2    | vs_hub.ch1_25 | 2012-10-03 15:48:33.93128+00
1.1.1.1 | device1    | vs_hub.ch1_25 | 2012-10-05 16:01:59.266779+00
1.1.1.2 | device2    | vs_hub.ch1_25 | 2012-10-05 16:13:46.843113+00
1.1.1.1 | device1    | vs_hub.ch1_25 | 2012-10-06 01:11:45.853361+00
1.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+00
1.1.1.2 | device2 | vs_hub.ch1_25 | 2012-10-02 17:48:01.755559+00

有办法吗？或者是否可以在临时表中为“objectid和devicename”选择最早的条目？

阅读 289

2021-04-22

共1个答案

小编典典

要提取描述的结果，这可能是最简单，最快的：

SELECT DISTINCT ON (devicename, objectid) *
FROM   tbl
ORDER  BY devicename, objectid, ts DESC;

从示例数据中，我得出结论，您将删除原始表的 大部分内容
。仅TRUNCATE将表（或DROP重新创建，因为您仍然应该添加代理pk列）并将剩余的行写入其中可能会更快。这还为您提供了一个原始表，该表以最适合您的查询的方式隐式集群（排序），并节省了其他工作VACUUM。总体而言，它可能仍然更快：

我也强烈建议向您的表（最好是一
serial 列）添加代理主键。

BEGIN;

CREATE TEMP TABLE tmp_tbl ON COMMIT DROP AS
SELECT DISTINCT ON (devicename, objectid) *
FROM   tbl
ORDER  BY devicename, objectid, 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, objectid, ts)
SELECT address, devicename, objectid, ts
FROM   tmp_tbl;

COMMIT;

在事务中完成所有操作，以确保您不会中途失败。

只要您的设置temp_buffers足够大以容纳临时表，这便会很快。否则，系统将开始将数据交换到磁盘，从而降低性能。您可以temp_buffers像这样为当前会话进行设置：

SET temp_buffers = 1000MB;

因此，您不会浪费通常不需要的RAM
temp_buffers。必须在会话中首次使用任何临时对象之前进行设置。

另外，在事务内部进行INSERT以下操作时TRUNCATE，在“预写日志”中将很容易-从而提高了性能。

考虑CREATE TABLE AS替代路线：

是什么导致大型INSERT减慢速度和磁盘使用量爆炸式增长？

唯一的缺点：您需要在表上设置 排他锁 。在并行负载很重的数据库中，这可能是个问题。

最后，切勿将其timestamp用作列名。它是每个SQL标准中的保留字，而在PostgreSQL中是类型名。我ts改用了。

2021-04-22