Deleting Duplicates without primary keys

1

Hi ,

I am trying this query to delete duplicates from my Database

DELETE FROM table_name
where ctid NOT IN (
SELECT MIN(ctid)
FROM table_name
GROUP BY ‘column_names’
Having count(*) > 1
)

Now it was running fine when data was less but as data size and no of rows crossed 10 million this query was going into timeout.
I even made hypertable on the table which i am running this query
please let me know how can i succesfully run this query

2

Hi @Viraj_Raul , it will be a very expensive query.

Think that if the where clause does not contain the time range specified, it means that it will need to navigate into all chunks for row by row all the time.

You can try to create another table from a query that finds the repeated cases and then you can actually delete them one by one.