Re: Delete duplicate records with same fields

Hi,

For finding duplicates you can use ctid.

select o.ctid, o.a, o.b from test o
where exists ( select 'x'
from test i
where i.a = o.a
and i.b = o.b
and i.ctid < o.ctid
);

for deleting,
delete from test
where exists ( select 'x'
from test i
where i.a = test.a
and i.b = test.b
and i.ctid < test.ctid
);

On Thu, Aug 29, 2013 at 5:09 PM, Arun P.L <arunpl(at)hotmail(dot)com> wrote:

> Hi all,
>
> Is there any way to delete duplicate rows in psql with no unique fields?
> I have a set of old records with their duplicates in my db and they are
> all having the same fields. How can I delete duplicates?
>
> Thanks in Advance,
> Arun
>

--
Hari