Thread: Delete duplicate records with same fields

Delete duplicate records with same fields

From

Arun P.L

Date:

29 August 2013, 14:39:07

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

Re: Delete duplicate records with same fields

From

Hariraman Jayaraj

Date:

29 August 2013, 15:15:56

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@hotmail.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