Skip site navigation (1) Skip section navigation (2)

Re: removing specific duplicates

From: Josh Jore <josh(at)greentechnologist(dot)org>
To: Al Arduengo <exal(at)oasis(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: removing specific duplicates
Date: 2002-07-15 14:46:15
Message-ID: Pine.BSO.4.44.0207150852500.24140-100000@kitten.greentechnologist.org (view raw or flat)
Thread:
Lists: 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(at)postgresql(dot)org)
>


In response to

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-07-15 16:16:44
Subject: Re: POSTGRESQL for WINDOWS NT4
Previous:From: Hano de la RouviereDate: 2002-07-15 13:39:52
Subject: Re: How can I tell what port my postgreSQL server is running on?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group