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?