Re: Deleting one of 2 identical records

On 6 September 2011 18:39, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com> wrote:

> Hi:****
>
> ** **
>
> If I have a table that has 2 records which are identical with regard to all
> their column values, is there a way to delete one of them, leaving one
> remaining? Is there some unique record_id key of some sort I can use for
> somethign like this?****
>
> ** **
>
> Thanks in Advance!****
>

Yes, identify them by their ctid value.

So get the ctids by running:

SELECT ctid, *
FROM my_table
WHERE <clause to identify duplicate rows>

You will see entries which look like "(7296,11)".

You can then delete the row by referencing it in the DELETE statement. For
example:

DELETE FROM my_table
WHERE ctid = '(7296,11)';

It's a shame we don't have a LIMIT on the DELETE clause (looks at hackers).

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company