X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 90D9732AA4B for ; Thu, 21 Oct 2004 21:09:59 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 47904-04 for ; Thu, 21 Oct 2004 20:09:52 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id A9D6A32A9FD for ; Thu, 21 Oct 2004 21:09:52 +0100 (BST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 30C3D3595A; Thu, 21 Oct 2004 13:09:50 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 2F41C35958; Thu, 21 Oct 2004 13:09:50 -0700 (PDT) Date: Thu, 21 Oct 2004 13:09:50 -0700 (PDT) From: Stephan Szabo To: Kent Anderson Cc: "Pgsql-Sql@Postgresql. Org" Subject: Re: Finding duplicated values In-Reply-To: Message-ID: <20041021130815.Y66555@megazone.bigpanda.com> References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/153 X-Sequence-Number: 19256 On Thu, 21 Oct 2004, Kent Anderson wrote: > I have a few tables that have duplicated values from an import from a > different database. I have two keys I tried to set as primary and got an > error > ERROR: could not create unique index > DETAIL: Table contains duplicated values. > > Is there some join I can use to compare the hmhmkey, wmwmkey pairs against > the table to find duplicate values? Each pair key should be unique but the > old database was less than normalized. Maybe SELECT hmhmkey, wmwmkey FROM exceptions GROUP BY hmhmkey, wmwmkey HAVING count(*)>1;