Thread: Deleting duplicate rows using ctid ?

Deleting duplicate rows using ctid ?

From
David Gauthier
Date:
I have a table with 4 columns, none of them unique.  I want to delete all but one of the duplicate records.  I think there's a way to do this with ctid.  Examples that pop up in google searches always rely on something like a unique (id) field, like a primary key,  (no good in my case)

create table dog (variety varchar, name varchar, color varchar, age int);
insert into dogs 
  ('lab','moby','white',12),
  ('lab','moby','white',12),
  ('spaniel','max','black',13),
  ('spaniel','max','black'),13,
  ('lab','moby','white',12);

I want the result to be just 2 recs, one for each dog.





Re: Deleting duplicate rows using ctid ?

From
"David G. Johnston"
Date:
On Mon, Feb 5, 2024 at 4:09 PM David Gauthier <dfgpostgres@gmail.com> wrote:

I want the result to be just 2 recs, one for each dog.

My present goto link for this question:


David J.

Re: Deleting duplicate rows using ctid ?

From
Andreas Kretschmer
Date:

Am 06.02.24 um 00:32 schrieb David G. Johnston:
> On Mon, Feb 5, 2024 at 4:09 PM David Gauthier <dfgpostgres@gmail.com> 
> wrote:
>
>
>     I want the result to be just 2 recs, one for each dog.
>
>
> My present goto link for this question:
>
> https://harshitjain.home.blog/2019/06/17/postgresql-how-to-delete-duplicate-rows-in-postgresql/
>
> David J.
>

postgres=# select * from dogs;
  dog
------
  dog1
  dog1
  dog2
  dog2
  dog2
  dog3
(6 rows)

postgres=# select ctid, dog, row_number() over (partition by dog) from 
dogs ;
  ctid  | dog  | row_number
-------+------+------------
  (0,1) | dog1 |          1
  (0,2) | dog1 |          2
  (0,3) | dog2 |          1
  (0,4) | dog2 |          2
  (0,5) | dog2 |          3
  (0,6) | dog3 |          1
(6 rows)

postgres=# with ct as (select ctid, dog, row_number() over (partition by 
dog) from dogs) delete from dogs where ctid in (select ctid from ct 
where row_number != 1) ;
DELETE 3
postgres=# select * from dogs;
  dog
------
  dog1
  dog2
  dog3
(3 rows)

postgres=#


Regards, Andreas


-- 
Andreas Kretschmer
CYBERTEC PostgreSQL Services and Support