Return-Path: owner-postman Received: from localhost (localhost [127.0.0.1]) by nobozo.CS.Berkeley.EDU (8.6.3/8.6.3) with SMTP id PAA01999 for postgres-dist; Tue, 9 Nov 1993 15:45:32 -0800 Resent-From: POSTGRES mailing list Resent-Message-Id: <199311092345.PAA01999@nobozo.CS.Berkeley.EDU> Sender: owner-postman@postgres.Berkeley.EDU X-Return-Path: owner-postman Received: from faerie.CS.Berkeley.EDU (faerie.CS.Berkeley.EDU [128.32.149.14]) by nobozo.CS.Berkeley.EDU (8.6.3/8.6.3) with ESMTP id PAA01990 for ; Tue, 9 Nov 1993 15:45:32 -0800 Received: from localhost (localhost [127.0.0.1]) by faerie.CS.Berkeley.EDU (8.6.3/8.1B) with SMTP id PAA06766; Tue, 9 Nov 1993 15:45:23 -0800 Message-Id: <199311092345.PAA06766@faerie.CS.Berkeley.EDU> X-Authentication-Warning: faerie.CS.Berkeley.EDU: Host localhost didn't use HELO protocol From: aoki@postgres.Berkeley.EDU (Paul M. Aoki) To: Brian Holman Cc: postgres@postgres.Berkeley.EDU Subject: Re: Removing Duplicate Records In-reply-to: Your message of Tue, 9 Nov 1993 15:42:39 +119303928 (MST) <9311092242.AA26379@liblas.byu.edu> Date: Tue, 09 Nov 93 15:45:22 -0800 X-Sender: aoki@postgres.Berkeley.EDU Resent-To: postgres-dist@postgres.Berkeley.EDU X-Mts: smtp Resent-Date: Tue, 09 Nov 93 15:45:32 -0800 Resent-XMts: smtp Brian Holman writes: > So I want to be able to list those records that are duplicates so that I can > 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 | aoki@postgres.Berkeley.EDU | Berkeley, CA 94720 | ...!uunet!ucbvax!aoki