Re: Deleting one of 2 identical records

On 9/6/2011 12:44 PM, Thom Brown wrote:
> On 6 September 2011 18:39, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com
> <mailto: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

I wonder.. using the new writeable cte's, could you:

with x (
-- id = 5 has two identical rows, but limit 1
select * from orig where id = 5 limit 1;
)
delete from x;

-Andy