From: Tim Landscheidt <tim@tim-landscheidt.de>
To: pgsql-sql@postgresql.org
Subject: Re: Duplicates Processing
Date: Fri, 08 Oct 2010 18:42:25 +0000
Message-ID: <m3iq1cr0fi.fsf@passepartout.tim-landscheidt.de> (raw)
References: <AANLkTinSF+zqTVHbHp5HgN0TwHX-DjLAwQ4LhE7FYFxZ@mail.gmail.com>
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
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: tim@tim-landscheidt.de
Subject: Re: Duplicates Processing
In-Reply-To: <m3iq1cr0fi.fsf@passepartout.tim-landscheidt.de>
* 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