Mail-Followup-To: pgsql-sql@postgresql.org
To: pgsql-sql@postgresql.org
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: Duplicates Processing
Date: Fri, 08 Oct 2010 18:42:25 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 33
Message-ID: <m3iq1cr0fi.fsf@passepartout.tim-landscheidt.de>
References: <AANLkTinSF+zqTVHbHp5HgN0TwHX-DjLAwQ4LhE7FYFxZ@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:L6k2QPhTPuD81CAyqabvPOHBWr8=

Gary Chambers <gwchamb@gmail.com> wrote:

> I've been provided a CSV file of parts that contains duplicates of
> properties (e.g. resistors have a wattage, tolerance, and temperature
> coefficient property) of those parts that differ by a manufacturer
> part number.  What I'd like to do is to process this file and, upon
> encountering one of the duplicates, take that part with its new part
> number and move it to a part substitutes table.  It seems like it
> should be pretty simple, but I can't seem to generate a query or a
> function to accomplish it.  I'd greatly appreciate any insight or
> assistance with solving this problem.  Thank you very much in advance.

You can - for example - create a query with a call to
ROW_NUMBER() and then process the matching rows (untested):

| 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);

Tim