From: | salah jubeh <s_jubeh(at)yahoo(dot)com> |
---|---|
To: | Thom Brown <thom(at)linux(dot)com>, "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com> |
Cc: | Andy Colson <andy(at)squeakycode(dot)net>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Deleting one of 2 identical records |
Date: | 2011-09-06 23:55:47 |
Message-ID: | 1315353347.86600.YahooMailNeo@web161503.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello Thom.
what is the meaning of
select table_name from table_name
Also is this a common behavior of all Databases i.e. oracle , Microsoft ,...etc . i.e is this is the standard behavior
I think this is a good way to find duplicates in general, I will write a routine to compare all the columns by excluding the primary key which is serial
Thanks in advance
Regards
________________________________
From: Thom Brown <thom(at)linux(dot)com>
To: "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>
Cc: Andy Colson <andy(at)squeakycode(dot)net>; "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, September 7, 2011 12:50 AM
Subject: Re: [GENERAL] Deleting one of 2 identical records
On 6 September 2011 19:00, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com> wrote:
The identification and deleting of the records using ctids seems to have worked fine.
>Thanks !
>
Alternative you could do something like this:
WITH keep AS (
SELECT
my_table AS duplicate_row,
min(ctid) AS keep,
count(*)
FROM my_table
GROUP BY my_table
HAVING count(*) > 1
)
DELETE FROM my_table
USING keep
WHERE
my_table = keep.duplicate_row
AND
my_table.ctid != keep
RETURNING my_table.ctid, my_table.*;
This would delete all duplicate rows from the table and just keep whichever row appears first in the table before its duplicates.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2011-09-07 00:05:57 | Re: Deleting one of 2 identical records |
Previous Message | Thom Brown | 2011-09-06 22:50:56 | Re: Deleting one of 2 identical records |