PostgreSQL - Delete duplicated records - ERROR: too many range table entries

Question

I have a HyperTable (TimescaleDB extension) called "conferimenti"

I am trying to delete about 2500 duplicated rows

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);

throws an error ERROR: too many range table entries SQL state: 54000

Executing this query i have a one column "id" with all the ids

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

I cannot disable triggers

score 2 · Answer 1 · answered May 20 '20 at 18:23

The sql state 5400 is for a "program limit exceeded", but there is nothing specifically for "ERROR: too many range table entries". Further you indicate that you "cannot disable triggers" which leads to the conclusion this is an internally generated application error; not a Postgres generated error. Seems like someone has established a Business Rule limiting the number of deletes. You need to investigate and determine that limit. Then revise you delete state to delete no more that that value.

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> );

Then run it multiple times as needed.

PostgreSQL - Delete duplicated records - ERROR: too many range table entries

1 Answers1