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 933D452803 for ; Wed, 25 May 2005 13:58:21 -0300 (ADT) 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 46144-07 for ; Wed, 25 May 2005 16:58:14 +0000 (GMT) Received: from iracema.dnscenter.com.br (iracema.dnscenter.com.br [67.15.81.98]) by svr1.postgresql.org (Postfix) with ESMTP id 41B7652806 for ; Wed, 25 May 2005 13:58:13 -0300 (ADT) Received: from cpanel by iracema.dnscenter.com.br with local (Exim 4.51) id 1DazCq-0003Xt-80 for pgsql-sql@postgresql.org; Wed, 25 May 2005 13:58:08 -0300 Received: from 200.148.63.230 ([200.148.63.230]) by www.presserv.org (Horde) with HTTP for ; Wed, 25 May 2005 13:58:07 -0300 Message-ID: <20050525135807.x9p08co04v8gg0cw@www.presserv.org> Date: Wed, 25 May 2005 13:58:07 -0300 From: lucas@presserv.org To: pgsql-sql@postgresql.org Subject: Re: Duplicated records References: <20050524175931.4dp41vy7v10c4wc4@www.presserv.org> <20050525050346.GA29126@webserv.wug-glas.de> In-Reply-To: <20050525050346.GA29126@webserv.wug-glas.de> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format="flowed" Content-Disposition: inline Content-Transfer-Encoding: 7bit User-Agent: Internet Messaging Program (IMP) H3 (4.0) X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - iracema.dnscenter.com.br X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [32001 502] / [47 12] X-AntiAbuse: Sender Address Domain - presserv.org X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.27 tagged_above=0 required=5 tests=AWL, NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200505/216 X-Sequence-Number: 21529 Hi. Thanks for the article... But, I have read it and the query works very slow... My table have aprox. 180.000 records (correct) and in entire table it has aprox.360.000 records(duplicated)... I tried to execute a query to delete the duplicated records, but it worked very very slow... look: # select * from lanctos order by numos; numos | field1 | field2 | field3 |... 00001 | test | T2-2 | 2 |... 00001 | test | T2-2 | 2 |... 00002 | Blabla | 0 | ABC |... 00002 | Blabla | 0 | ABC |... 00003 | Llllll | Oooooo | Rrrrrr |... 00003 | Llllll | Oooooo | Rrrrrr |... ... The records is entire duplicated (with all fields having the same data), thinking the "numos" fields as primary key I have executed the query: # DELETE from lanctos where not oid=(select oid from lanctos as l2 where l2.numos=lanctos.numos limit 1); I have tested others querys with EXPLAIN command to examine the performance time, and this query was the best performance I got... but its is slow. Other query is: # DELETE from lanctos where not exists (select '1' from lanctos as l2 where l2.numos=lanctos.numos and not l2.oid=lanctos.oid); Is there a way to delete those duplicated records faster??? Remembering the table have aprox 360.000 records... Is better I create other table and copy those data??? How should I created??? Thanks. Quoting Andreas Kretschmer : > am 24.05.2005, um 17:59:31 -0300 mailte lucas@presserv.org folgendes: >> Hi. >> How can I delete the duplicated records with "DELETE FROM TABLE WHERE..." >> clause?? > > Please read http://www.gtsm.com/oscon2003/deletetid.html > > Its a very good article about this problem. >