CODEWITHSUNDEEP
postgresqltimescaledbhypertable
Augusto Murri
Augusto Murri

Reputation: 323

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

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

Upvotes: 3

Views: 1011

Answers (1)

Belayer
Belayer

Reputation: 14936

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.

Upvotes: 1

Related Questions