Delete rows that share the same geometry value in postgis

Question

Hello I have the following problem:

I created a grid in QGIS. For some unknown reason QGIS doesn't tesselate the plane correctly. Sometimes there are polygons with different "id" that represents the same portion of the area. I loaded the Shapefile into postgis (without noticing this) and began to have troubles in queries. When I look for the problem I saw that sometimes 2 or even 4 polygons represent exactly the same region. How could I delete those repeating polygons?

Answer 1

For my_table where gid is the primary key and geom is the geometry column, try:

DELETE FROM my_table
WHERE gid NOT IN (
    select max(dup.gid)
    from my_table as dup
    group by geom);

Note: make a backup of this table beforehand, in case things go wrong.