Re: How to delete completely duplicate rows - Mailing list pgsql-general

From Erik Darling
Subject Re: How to delete completely duplicate rows
Date
Msg-id CAO+EYw+GTLwb8trWwDxVCJ2jU-CYPYANhUEppiO9NyD1LpW3pg@mail.gmail.com
Whole thread Raw
In response to How to delete completely duplicate rows  ("Janek Sendrowski" <janek12@web.de>)
List pgsql-general

With C as (
Select row_number() over partition by (list, all, columns, here order by oid) as rn
)
Delete
From C
Where rn > 1;

On Jan 1, 2014 7:15 AM, "Janek Sendrowski" <janek12@web.de> wrote:
Hi,
 
I want to delete duplicates in my table. I've dropped the unique constraint to insert my data.
My id value is a hash calculated witch the values of the two other columns.
So I want to delete all columns, which are indentical, but keeping one.
 
DELETE FROM table t1 USING table t2 WHERE t1.id = t2.id AND t1.ctid > t2.ctid

But the oids aren't unique enough.
What else could I do?

Janek


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: "Janek Sendrowski"
Date:
Subject: How to delete completely duplicate rows
Next
From: Jayadevan M
Date:
Subject: Refresh Materialized View