Message-ID: <4A609310.4050003@sympatico.ca>
Date: Fri, 17 Jul 2009 11:04:48 -0400
From: Frank Bax <fbax@sympatico.ca>
User-Agent: Thunderbird 2.0.0.19 (X11/20090301)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: De-duplicating rows
References: <87E8E647-4CAD-4491-B687-FA763E0EBE7E@thebuild.com>
	<4A60265D.1060905@archonet.com>
In-Reply-To: <4A60265D.1060905@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.