Subject: Removing Duplicate Records

From: Brian Holman <[email-masked]>
Date: Tue, 9 Nov 1993 15:42:39 +119303928 (MST)

I have created the following class in a database:

PATRON (ssn = char16, name = text, status = char, pin = int2)

The class contains approximately 50,000 records loaded in from various flat
files.  However, there are some duplicate records in the class.  In other
words, there might be two or more records with the same ssn (social security
number) and I only want one record per social security number.

So I want to be able to list those records that are duplicates so that I can
remove them.  I haven't been able to figure out how to construct a query
that will do this.  It seems like it should be somewhat trivial.  Any help
from you Postquel experts out there would be greatly appreciated.
    
-- 
+--------------------------+-------------------------------------+
| BRIAN K. HOLMAN          | E-Mail:        [email-masked] |
| Programmer/Analyst       | Mail:    2330 HBLL, Provo, UT 84602 |
| Library Automations      | Phone:               (801) 378-8162 |
| Brigham Young University | Fax:                 (801) 378-3221 |
+--------------------------+-------------------------------------+

---

From: [email-masked] (Paul M. Aoki)
Date: Tue, 09 Nov 93 15:45:22 -0800

Brian Holman <[email-masked]> writes:
> remove them.

well, if they are complete dups, then there is
	retrieve into GOOD_PATRONS unique (PATRONS.all)
presumably they aren't or you would have done this.

retroactively enforcing some kind of key is harder -- it would be really 
easy if aggregates worked right, but they don't.  you should be able to say
	retrieve (PATRONS.ssn) from dups in PATRONS 
		where count{PATRONS.ssn where PATRONS.ssn = dups.ssn} > 1
(i think) but, again, you can't because aggregates don't work in the 
qualification :-(  if aggregates that reference >1 class worked correctly,
you could get something like this (but with 2 queries) to work, too..

so for this problem my only suggestion is writing a pgperl script :-P
any other ideas?
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki