Received: from bastuba.partitur.se (bastuba.partitur.se [193.219.246.194]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA26826 for ; Fri, 7 Apr 2000 07:27:48 -0400 (EDT) (envelope-from kudo@partitur.se) Received: from partitur.se (tb303.partitur.se [193.219.246.230]) by bastuba.partitur.se (8.8.8/8.8.8) with ESMTP id NAA25287; Fri, 7 Apr 2000 13:27:38 +0200 (CEST) (envelope-from kudo@partitur.se) Message-ID: <38EDC62A.77BBB73D@partitur.se> Date: Fri, 07 Apr 2000 13:27:38 +0200 From: Patrik Kudo X-Mailer: Mozilla 4.72 [en] (X11; I; FreeBSD 4.0-STABLE i386) X-Accept-Language: en MIME-Version: 1.0 To: Allan Kelly CC: "pgsql-sql@postgresql.org" Subject: Re: duplicates References: <38EDB999.EA91E2DB@buildstore.co.uk> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200004/65 Hi Allan Firstly I'd suggest a unique index on the column that should be unique to force uniqueness on that column. But since you already have double tuples, you can do the following: delete from subscribers where exists ( select 1 from subscribers s where s.user_name = subscribers.user_name and s.oid > subscribers.oid) I'm not 100% certain it'll do the work right, so PLEASE try it out with a testtable first =) Regards, Patrik Kudo Allan Kelly wrote: > > I have a system bug which means that I have occasional dupicate entries in > my 'subscribers' table. I'm trying to find a query which performs something > like a 'where user_name is not unique' query. > > At the moment we use this cludge: > > select count(*), user_name from subscribers > group by user_name order by count; > > (I'm surprised I can't add 'where count > 1' but it seems conditionals on > aggregate fields are not allowed). > > This gives me a very long list with the 'not unique' entries at the bottom, eg > > count | user_name > ------+------------------ > 1 | bill.hicks > [ ..cut 9 zillion results.. ] > 1 | margaret.thatcher > 4 | linus.torvalds > 9 | bill.gates > > I then have to do > > select oid from subscribers where user_name = 'linus.torvalds'; > > and delete all but one of the records. Is there a better way to do this? > And yes, we're working on a system fix to avoid the problem in the 1st place! > > TIA, al.