时间戳

首页 > 解决方案 > PostgreSQL - 删除重复记录 - 错误：范围表条目过多

postgresql - PostgreSQL - 删除重复记录 - 错误：范围表条目过多

问题描述

我有一个名为“conferimenti”的 HyperTable（TimescaleDB 扩展）

我正在尝试删除大约 2500 个重复的行

DELETE FROM conferimenti
WHERE id IN
    (SELECT id
    FROM 
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY dataora, idcomune, codicestazione, tiporifiuto, codicetag
        ORDER BY  id ) AS row_num
        FROM conferimenti ) t
        WHERE t.row_num > 1);

抛出错误 ERROR: too many range table entries SQL state: 54000

执行此查询我有一个包含所有 id 的一列“id”

SELECT id
    FROM 
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY dataora, idcomune, codicestazione, tiporifiuto, codicetag
        ORDER BY  id ) AS row_num
        FROM conferimenti ) t
        WHERE t.row_num > 1

我无法禁用触发器

标签： postgresqltimescaledbhypertable

解决方案

sql 状态5400 用于“超出程序限制”，但没有专门针对“错误：范围表条目太多”。此外，您指出您“无法禁用触发器”，从而得出结论这是内部生成的应用程序错误；不是 Postgres 生成的错误。似乎有人已经建立了限制删除次数的业务规则。您需要调查并确定该限制。然后修改您的删除状态以删除该值。

DELETE FROM conferimenti
WHERE id IN
    (SELECT id
    FROM 
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY dataora, idcomune, codicestazione, tiporifiuto, codicetag
        ORDER BY  id ) AS row_num
        FROM conferimenti ) t
   WHERE t.row_num > 1
   LIMIT <business_rule_max> );

然后根据需要多次运行。

推荐阅读