Re: removing specific duplicates - Mailing list pgsql-novice

Al,
First thing you should do is add a serial not null unique column to that
table. If you are going to need to do this sort of operation then you need
something by which to get a handle to a specific row.

So this command will delete exactly one duplicate from your table. You
need to keep runnin it until it deletes zero rows. Is this idea? No. It's
one step above worst case. Unless you *like* this sort of pain, fix your
app to prevent this sort of  duplication. You could have a trigger or rule
stop duplicate inserts. You could use a unique index. Whatever works for
you. Just do *something*.

DELETE FROM temp WHERE oid IN
    (
    SELECT    temp.oid
    FROM    temp
    JOIN
    ( SELECT count(*), sample, spec, temp, measurement
      FROM temp
      GROUP BY sample, spec, temp, measurement ) dup
    USING (sample, spec, temp, measurement)
    WHERE dup.count > 1
    LIMIT 1;


So your next question. How to replace the existing unique data with a
single averaage. Requesting the average is easy:

> sample  spec  temp  voltage  measurement
> 1           Tdsu  25      4.5       1.12e-9
> 1           Tdsu  25      4.5       1.3e-9

SELECT sample, spec, temp, voltage, AVG(measurement)
FROM temp GROUP BY sample, spec, temp, voltage;

So if you wanted to keep the average and throw away the detail data then
just delete the old row and insert the new row (which you saved prior to
deleting the data)


Joshua b. Jore ; http://www.greentechnologist.org

On Fri, 12 Jul 2002, Al Arduengo wrote:

> I have a table of characterization data from a test of a certain chip where
> I work. THe table consists of 5 columns:
>
> sample int
> spec text
> temp int
> voltage float
> measurement float
>
> Each sample chip (1-120) is tested at three temps and two voltages. The
> column of interest is measurement. The problem is that some of the samples
> were tested multiple times for one temp/voltage combination so I have
> entries such as:
>
>
> My requirement is to delete any duplicate rows (such as #2 in this case)
> and then take the average of #1 and #3, replace #1 with that average
> measurement and delete #3. I simply cannot figure out how to first get rid
> of *just* #2 and then somehow find the case of #1 and #3 existing and then
> do the average followed by removing #3. Using DISTINCT with SELECT will
> obviously spit out #1 and #3 but that doesn't get me anywhere. I am
> somewhat of a SQL novice so please have pity on me in your possible
> explanations. My ultimate goal is to do these modifications without having
> to go in and manually find these situations and then manually make the
> changes. I think it is possible with SQL but I can't figure out how.
>
> Thanks very much in advance.
> -Al Arduengo
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>