I have a = few tables=20 that have duplicated values from an import from a different datab= ase.=20 I have two keys I tried to set as primary and got an error

ERROR:&nb= sp; could=20 not create unique index
DETAIL: Table contains duplicated=20 values.

Is there = some join I=20 can use to compare the hmhmkey, wmwmkey pairs against the table to find=20 duplicate values? Each pair key should be unique but the old database was l= ess=20 than normalized.

I was trying to use = the code=20 below but it returned no rows.

SELECT hm= hmkey,=20 wmwmkey
FROM exceptions
EXCEPT
SELECT hmhmkey, wmwmke= y=20

FROM = ;=20 exceptions;

Any=20 suggestions?

Kent Anderson

EZYield.com

407-629-0900

www.ezyield.com

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

This electronic message transmission conta= ins=20 information from the Company that may be proprietary, confidential and/or= =20 privileged. The information is intended only for the use of the individual(= s) or=20 entity named above. If you are not the intended recipient, be aware t= hat=20 any disclosure, copying or distribution or use of the contents of this=20 information is prohibited. If you have received this electronic=20 transmission in error, please notify the sender immediately by replying to = the=20 address listed in the "From:" field.

------=_NextPart_000_0002_01C4B77E.7AC7C010-- From bricklen@zyahoo.zcomz Thu Oct 21 19:21:03 2004 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 A4D3432A923 for ; Thu, 21 Oct 2004 20:21:03 +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 34501-01 for ; Thu, 21 Oct 2004 19:20:47 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 261F532A8DF for ; Thu, 21 Oct 2004 20:20:47 +0100 (BST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i9LJKjJ7034619 for ; Thu, 21 Oct 2004 19:20:45 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i9LJKfuQ034603 for pgsql-sql@postgresql.org; Thu, 21 Oct 2004 19:20:41 GMT From: Bricklen User-Agent: Mozilla Thunderbird 0.8 (X11/20040926) X-Accept-Language: en-us, en MIME-Version: 1.0 X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Finding duplicated values References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Lines: 28 Message-ID: Date: Thu, 21 Oct 2004 19:20:42 GMT To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.1 tagged_above=0.0 required=5.0 tests=NO_DNS_FOR_FROM X-Spam-Level: * X-Archive-Number: 200410/176 X-Sequence-Number: 19279 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. > > I was trying to use the code below but it returned no rows. > > SELECT hmhmkey, wmwmkey > FROM exceptions > EXCEPT > SELECT hmhmkey, wmwmkey > FROM exceptions; > > Any suggestions? > > Kent Anderson > EZYield.com > 407-629-0900 > www.ezyield.com Try http://archives.postgresql.org/pgsql-sql/1999-03/msg00239.php From sszabo@megazone.bigpanda.com Thu Oct 21 20:09:59 2004 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; From lorid@dri.edu Fri Oct 22 05:11:10 2004 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 A157F329D22 for ; Fri, 22 Oct 2004 06:11:10 +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 85729-10 for ; Fri, 22 Oct 2004 05:11:03 +0000 (GMT) Received: from screen.dri.edu (screen.dri.edu [134.197.100.251]) by svr1.postgresql.org (Postfix) with ESMTP id 3885C32AFEE for ; Fri, 22 Oct 2004 06:11:04 +0100 (BST) Received: (from fw_mx@localhost) by screen.dri.edu (8.9.1b+Sun/8.9.1) id QAA07424 for ; Thu, 21 Oct 2004 16:11:59 -0700 (PDT) Received: from derringer.dri.edu( 10.10.9.111) by screen.dri.edu via smap (V2.1.dri) id xma007422; Thu, 21 Oct 04 16:11:57 -0700 Received: from dri.edu (ppp16.dri.edu [10.10.8.216]) by derringer.dri.edu (8.9.3+Sun/8.9.1) with ESMTP id QAA17861; Thu, 21 Oct 2004 16:10:56 -0700 (PDT) Message-ID: <417843C8.6060406@dri.edu> Date: Thu, 21 Oct 2004 16:18:32 -0700 From: lorid User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7a) Gecko/20040219 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Kent Anderson Cc: "Pgsql-Sql@Postgresql. Org" Subject: Re: Finding duplicated values References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------000104040401020407000003" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.4 tagged_above=0.0 required=5.0 tests=HTML_30_40, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: * X-Archive-Number: 200410/158 X-Sequence-Number: 19261 This is a multi-part message in MIME format. --------------000104040401020407000003 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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. > > I was trying to use the code below but it returned no rows. > > SELECT hmhmkey, wmwmkey > FROM exceptions > EXCEPT > SELECT hmhmkey, wmwmkey > FROM exceptions; > > Any suggestions? > > Kent Anderson > EZYield.com > 407-629-0900 > www.ezyield.com > > ======================================== > This electronic message transmission contains information from the > Company that may be proprietary, confidential and/or privileged. The > information is intended only for the use of the individual(s) or > entity named above. If you are not the intended recipient, be aware > that any disclosure, copying or distribution or use of the contents of > this information is prohibited. If you have received this electronic > transmission in error, please notify the sender immediately by > replying to the address listed in the "From:" field. > ****************************** This might do it... If you do this on the table that had duplicates you wont need to join select count(hmhmkey),count(wmwmkey) from exceptions group by hmhmkey,wmwmkey having count(hmhmkey) >1 or having count(wmwmkey) >1; --------------000104040401020407000003 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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.

I was trying to use the code below but it returned no rows.

SELECT hmhmkey, wmwmkey
FROM exceptions
EXCEPT
SELECT hmhmkey, wmwmkey

FROM exceptions;

Any suggestions?

Kent Anderson

EZYield.com

407-629-0900

www.ezyield.com

========================================

This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.

******************************
This might do it...
If you do this on the table that had duplicates you wont need to join
select count(hmhmkey),count(wmwmkey) from
exceptions group by hmhmkey,wmwmkey having count(hmhmkey) >1 or having count(wmwmkey) >1;

--------------000104040401020407000003-- From ch@rodos.fzk.de Fri Oct 22 07:49:39 2004 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 8684432B116 for ; Fri, 22 Oct 2004 08:49:39 +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 24694-08 for ; Fri, 22 Oct 2004 07:49:30 +0000 (GMT) Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by svr1.postgresql.org (Postfix) with SMTP id C242C32B10D for ; Fri, 22 Oct 2004 08:49:34 +0100 (BST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Fri Oct 22 09:49:30 2004 +0200 Received: from rodos.fzk.de (resy6.fzk.de [141.52.68.10]) by resy5.fzk.de (8.12.7/8.12.7/SuSE Linux 0.6) with ESMTP id i9M7nTtj015658; Fri, 22 Oct 2004 09:49:29 +0200 Message-ID: <4178BB88.D7175ADC@rodos.fzk.de> Date: Fri, 22 Oct 2004 09:49:28 +0200 From: Christoph Haller X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780) X-Accept-Language: en MIME-Version: 1.0 To: Kent Anderson , pgsql-sql@postgresql.org Subject: Re: Finding duplicated values References: Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS - amavis-milter (http://www.amavis.org/) 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/159 X-Sequence-Number: 19262 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.I was trying to use the code below but it > returned no rows.SELECT hmhmkey, wmwmkey > FROM exceptions > EXCEPT > SELECT hmhmkey, wmwmkey FROM exceptions;Any suggestions?Kent > Anderson You might want to search the [SQL] archive on the following topics for more inspiration: selecting duplicate records Delete 1 Record of 2 Duplicate Records Regards, Christoph