Konubinix' opinionated web of thoughts

Deduplicate Postgresql Rows


Provided there is no already existing id columns.

alter table mytable add column id serial

Then, partition other some commun attribute and find the row number.

delete from mytable where id in (
        select id from (
               select id, cid, row_number() over(partition by cid) from mytable -- group by the cid and show the row number
        ) as withrows where row_number > 1 -- find only the ones with more than 1 match
) -- remove them

Then, remove the temporary id column.

alter table mytable drop column id