From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: De-duplicating rows
Date: Fri, 17 Jul 2009 11:04:48 -0400
Message-ID: <4A609310.4050003@sympatico.ca> (raw)
In-Reply-To: <4A60265D.1060905@archonet.com>
References: <87E8E647-4CAD-4491-B687-FA763E0EBE7E@thebuild.com>
<4A60265D.1060905@archonet.com>
Richard Huxton wrote:
> Christophe wrote:
>> Now, since this database has been production since 7.2 days, cruft has
>> crept in: in particular, there are duplicate email addresses, some
>> with mismatched attributes. The policy decision by the client is that
>> the correct row is the one with the earliest timestamp.
>
> Something like (untested):
>
> CREATE TEMPORARY TABLE earliest_duplicates AS
> SELECT
> email AS tgt_email,
> min(create_date) AS tgt_date
> FROM mytable
> GROUP BY email
> HAVING count(*) > 1;
>
> DELETE FROM mytable USING earliest duplicates
> WHERE email=tgt_email AND create_date > tgt_date;
If it is possible that two rows exist for the same email/date; then you
will likely need to deal with these manually.
If you rerun the above SELECT after running the delete you should
identify these rows.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fbax@sympatico.ca
Subject: Re: De-duplicating rows
In-Reply-To: <4A609310.4050003@sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox