Message-ID: <4CAF79EB.1080606@gmail.com>
Date: Fri, 08 Oct 2010 14:07:07 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.11) Gecko/20100714 SUSE/3.0.6 Lightning/1.0b1
	Thunderbird/3.0.6
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
References: <AANLkTinSF+zqTVHbHp5HgN0TwHX-DjLAwQ4LhE7FYFxZ@mail.gmail.com>
	<m3iq1cr0fi.fsf@passepartout.tim-landscheidt.de>
	<AANLkTinr=cNsOeMGfOAjg5ncWhQ9p4RbpfyaoV1YN66T@mail.gmail.com>
In-Reply-To: <AANLkTinr=cNsOeMGfOAjg5ncWhQ9p4RbpfyaoV1YN66T@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Subject: Re: Duplicates Processing


On 10/08/2010 01:42 PM, Gary Chambers wrote:
> Tim,
> 
> Thanks for taking the time to reply!
> 
>> | INSERT INTO substitutes ([...])
>> |   SELECT [...] FROM
>> |     (SELECT *,
>> |             ROW_NUMBER() OVER (PARTITION BY wattage, tolerance, temperature
>> |                         ORDER BY part_number) AS RN
>> |      FROM parts) AS SubQuery
>> |   WHERE RN > 1;
> 
>> | DELETE FROM parts
>> | WHERE primary_key IN
>> |   (SELECT primary_key FROM
>> |     (SELECT *,
>> |             ROW_NUMBER() OVER (PARTITION BY wattage, tolerance, temperature
>> |                                ORDER BY part_number) AS RN
>> |      FROM parts) AS SubQuery
>> |    WHERE RN > 1);
> 
> You have solved the problem precisely as I described it.  In my haste
> to make the request for assistance, I omitted one critical piece of
> information that may call into question my data model.  In its current
> state, my substitute parts table contains only the part number (the
> "new" one, so-to-speak), a foreign key reference to the original parts
> table, and some location data (which is also in the original parts
> table).  Is there any advice you can offer in light of what I have
> just described? I apologize for the oversight.
> 
> -- Gary Chambers
> 

Perhaps a trade off between nullable fields and redundant types.  If
your original table simply had a nullable column called
isReplacementFor, into which you place in the subsequent rows the id of
the first instance found.