Re: how to remove the duplicate records from a table - Mailing list pgsql-general

Yi Zhao wrote:
> I have a table contains some duplicate records, and this table create
> without oids, for example:
>  id | temp_id 
> ----+---------
>  10 |       1
>  10 |       1
>  10 |       1
>  20 |       4
>  20 |       4
>  30 |       5
>  30 |       5
> I want get the duplicated records removed and only one is reserved, so
> the results is:
> 10 1
> 20 4
> 30 5
> 
> I know create a temp table will resolve this problem, but I don't want
> this way:)
> 
> can someone tell me a simple methold?

Don't know if you'd call that simple, but if the table is
called "t", you could do

DELETE FROM t t1 USING t t2
WHERE t1.id = t2.id AND t1.temp_id = t2.temp_id AND t1.ctid > t2.ctid;

Yours,
Laurenz Albe