From: lucas@presserv.org
To: pgsql-sql@postgresql.org
Subject: Re: Duplicated records
Date: Wed, 25 May 2005 13:58:07 -0300
Message-ID: <20050525135807.x9p08co04v8gg0cw@www.presserv.org> (raw)
In-Reply-To: <20050525050346.GA29126@webserv.wug-glas.de>
References: <20050524175931.4dp41vy7v10c4wc4@www.presserv.org>
<20050525050346.GA29126@webserv.wug-glas.de>
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 <akretschmer@despammed.com>:
> 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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lucas@presserv.org
Subject: Re: Duplicated records
In-Reply-To: <20050525135807.x9p08co04v8gg0cw@www.presserv.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox