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 <t...@linux.com> To: "Gauthier, Dave" <dave.gauth...@intel.com> Cc: Andy Colson <a...@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.gauth...@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