Deleting duplicate rows using ctid ?

From: David Gauthier <dfgpostgres(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Deleting duplicate rows using ctid ?
Date: 2024-02-05 23:08:46
Message-ID: CAEs=6DnRvQoV_pCLOU91J9y=xQRKfiJFTdqpk+a4LJay0VeEwg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2024-02-05 23:32:25 Re: Deleting duplicate rows using ctid ?
Previous Message Максим Чистяков 2024-02-05 21:38:57 Log pre-master keys during TLS v1.3 handshake