DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=PKAQCGRXUiscL3sJZH7000Jb6HOHvYsvEKnqth4NCV3TTFdd0SgSFJypJBVrW5kQOp
	W4VuoUhL1PRL4Jt6tnsLtn1RO0ylHjzJcinlMYOntM4li/jKFDatOlOug4FHmp8UwGwD
	g4jAFD1a05K/f1cnZ5n3AuVx9Cf33hZtFyAP4=
MIME-Version: 1.0
In-Reply-To: <87E8E647-4CAD-4491-B687-FA763E0EBE7E@thebuild.com>
References: <87E8E647-4CAD-4491-B687-FA763E0EBE7E@thebuild.com>
Date: Fri, 17 Jul 2009 09:32:55 -0600
Message-ID: <dcc563d10907170832r1f41fdfcl2377f72bcdcb3dd8@mail.gmail.com>
Subject: Re: De-duplicating rows
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Christophe <xof@thebuild.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Thu, Jul 16, 2009 at 9:07 PM, Christophe<xof@thebuild.com> wrote:
> The Subject: is somewhat imprecise, but here's what I'm trying to do. =A0=
For
> some reason, my brain is locking up over it.
>
> I'm moving a 7.2 (yes) database to 8.4. =A0In the table in question, the
> structure is along the lines of:
>
> =A0 =A0 =A0 =A0serial_number =A0 SERIAL, PRIMARY KEY
> =A0 =A0 =A0 =A0email =A0 =A0 =A0 =A0 =A0 TEXT
> =A0 =A0 =A0 =A0create_date =A0 =A0 TIMESTAMP
> =A0 =A0 =A0 =A0attr1 =A0 =A0 =A0 =A0 =A0 type
> =A0 =A0 =A0 =A0attr2 =A0 =A0 =A0 =A0 =A0 type
> =A0 =A0 =A0 =A0attr3 =A0 =A0 =A0 =A0 =A0 type
> =A0 =A0 =A0 =A0...
>
> (The point of the "attr" fields is that there are many more columns for e=
ach
> row.)
>
> The new structure removes the "serial_number" field, and uses "email" as =
the
> primary key, but is otherwise unchanged:
>
> =A0 =A0 =A0 =A0email =A0 =A0 =A0 =A0 =A0 TEXT, PRIMARY KEY
> =A0 =A0 =A0 =A0create_date =A0 =A0 TIMESTAMP
> =A0 =A0 =A0 =A0attr1 =A0 =A0 =A0 =A0 =A0 type
> =A0 =A0 =A0 =A0attr2 =A0 =A0 =A0 =A0 =A0 type
> =A0 =A0 =A0 =A0attr3 =A0 =A0 =A0 =A0 =A0 type
> =A0 =A0 =A0 =A0...
>
> Now, since this database has been production since 7.2 days, cruft has cr=
ept
> in: in particular, there are duplicate email addresses, some with mismatc=
hed
> attributes. =A0The policy decision by the client is that the correct row =
is
> the one with the earliest timestamp. =A0(The timestamps are widely
> distributed; it's not the case that there is a single timestamp above whi=
ch
> all the duplicates live.) =A0Thus, ideally, I want to select exactly one =
row
> per "email", picking the row with the earliest timestamp in the case that
> there is more than one row with that email.
>
> Any suggestions on how to write such a SELECT? =A0Of course, I could do t=
his
> with an application against the db, but a single SELECT would be great if
> possible.

OK, assuming we can keep the serial number during the conversion, we
could use something like this:

select distinct a.serial_number from table a join table b on
(a.email=3Db.email and a.serial_number>b.serial_number)

Now assuming that the serial numbers and the timestamps are in order
together, that'll give us all the serial numbers for all the matching
email addresses EXCEPT the first one.  If the serial numbers are not
in order with the timestamps, then create a sequence, and update them
in order, then the query will work.  Once you've confirmed by hand
that the first hundred or so serial_numbers you're getting back ARE in
fact all n+1 for the same email address, use the select in a subselect
to delete:

delete from table x where serial_number in (select distinct....)