Re: Removing duplicate records from a bulk upload

On 12/7/2014 9:31 PM, Daniel Begin wrote:
> I have just completed the bulk upload of a large database. Some tables
> have billions of records and no constraints or indexes have been applied
> yet. About 0.1% of these records may have been duplicated during the
> upload and I need to remove them before applying constraints.
>
> I understand there are (at least) two approaches to get a table without
> duplicate records…
>
> -           Delete duplicate records from the table based on an
> appropriate select clause;
>
> -           Create a new table with the results from a select distinct
> clause, and then drop the original table.
>
> What would be the most efficient procedure in PostgreSQL to do the job
> considering …
>
> -           I do not know which records were duplicated;
>
> -           There are no indexes applied on tables yet;
>
> -           There is no OIDS on tables yet;
>
> -           The database is currently 1TB but I have plenty of disk space.
>
> Daniel
>

How would you detect duplicate?  Is there a single field that would be
duplicated?  Or do you have to test a bunch of different fields?

If its a single field, you could find dups in a single pass of the table
with:

create index bigtable_key on bigtable(key);
select key, count(*) from bigtable group by key having count(*) > 1;

Save that list, and decide on some way of deleting the dups.

The index might help the initial select, but will really help re-query
and delete statements.

-Andy