Postgres Pro
Downloads
Home   >   mailing lists

Re: Deleting one of 2 identical records - Mailing list pgsql-general

From salah jubeh
Subject Re: Deleting one of 2 identical records
Date
Msg-id 1315353347.86600.YahooMailNeo@web161503.mail.bf1.yahoo.com
Whole thread Raw
In response to Re: Deleting one of 2 identical records  (Thom Brown <thom@linux.com>)
Responses Re: Deleting one of 2 identical records  (Thom Brown <thom@linux.com>)
List pgsql-general
Tree view

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@linux.com>
To: "Gauthier, Dave" <dave.gauthier@intel.com>
Cc: Andy Colson <andy@squeakycode.net>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.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.gauthier@intel.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


pgsql-general by date:

Previous
From: Thom Brown
Date:
Subject: Re: Deleting one of 2 identical records
Next
From: Thom Brown
Date:
Subject: Re: Deleting one of 2 identical records