Re: how to remove the duplicate records from a table

Robert Treat 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:)
>>
>> DELETE FROM t t1 USING t t2
>> WHERE t1.id = t2.id AND t1.temp_id = t2.temp_id AND t1.ctid t2.ctid;
> 
> note that one problem the delete from approaches have that the temp table 
> solutions dont is that you can end up with a lot of dead tuples if there were 
> a lot of duplicates... so if you can afford the locks, its not a bad idea to 
> do begin; lock table t1 in access exclsuive mode; create temp table x as 
> select ... from t1; truncate t1; insert into t1 select * from x; create 
> unique index ui1 on t1(...); commit;  this way you're now unique table will 
> be nice and compacted, and wont get any more duplicate rows.  

Very true; an alternative way to achieve that is to
VACUUM FULL t
after deleting the duplicate rows.

As for the UNIQUE index, that's of course the right thing to do, but
I wasn't sure if Yi Zhao wanted to change the database "design".

At any rate, I had thought that a unique constraint was preferrable to
a unique index because - while doing the same thing - the former will
also show up in pg_catalog.pg_constraint.

Yours,
Laurenz Albe