Duplicate rows - how to remove one? I have a pretty large table (~114 million rows) containing OS MasterMap data. This is freshly loaded data in a new table. When trying to set the primary key, I get this error:

ERROR:  could not create unique index "tbl_os_mmap_topoarea_pkey"
DETAIL:  Key (toid)=(1000000004081308) is duplicated.


Somehow, I have ended up with an exactly duplicated row. Every field is the same in these two rows. I want to delete one row, but keep the other. As there is no way to distinguish between the two, how can this be done?

I would like to do this as quickly and simply as possible. Creating temporary tables etc. is not really an option as it would take too long on a dataset of this size. Creating a new unique ID column would be quicker I guess, but also probably take some time.

After a bit of research, I have learned that all records in postgres have a hidden unique id, the ctid. Can I use this to delete one of the duplicate rows?
This will work I think:
with d as 
  ( select ctid, row_number() over (partition by t.*) as rn 
    from tablename as t 
  ) 
delete from tablename as t 
using d 
where d.rn &gt; 1 
  and d.ctid = t.ctid ;

And another variation. Not sure which will be more efficient:
delete from tablename as t 
where exists 
      ( select * 
        from tablename as d 
        where d.ctid &gt; t.ctid 
          and d.* is not distinct from t.*
      ) ;

But note what the docs say abut ctid:

ctid
The physical location of the row version within its table. Note that although the ctid can be used to locate the row version very quickly, a row's ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row identifier. The OID, or even better a user-defined serial number, should be used to identify logical rows.

So, if the table was created WITH OIDS, use that instead.