Re: Deleting duplicate rows using ctid ?
Am 06.02.24 um 00:32 schrieb David G. Johnston: On Mon, Feb 5, 2024 at 4:09 PM David Gauthier 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
Re: Deleting duplicate rows using ctid ?
On Mon, Feb 5, 2024 at 4:09 PM David Gauthier 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.
Deleting duplicate rows using ctid ?
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.
