Re: 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