Re: How to delete duplicate rows?

On Thu, Feb 03, 2005 at 23:04:57 -0200,
Clodoaldo Pinto <clodoaldo(dot)pinto(at)gmail(dot)com> wrote:
> This one must be obvious for most here.
>
> I have a 170 million rows table from which I want to eliminate
> duplicate "would be" keys and leave only uniques.
>
> I found a query in http://www.jlcomp.demon.co.uk/faq/duplicates.html
> for the oracle database but can't figure out how to refer to the row
> id in postgresql:
>
> delete from test where rowid not in
> (select min(rowid) from test group by a,b);
>
> How to refer to the row id? Any better way to do it?

Your best bet is to use oid if the table has them. If it doesn't you
might want to use ctid. That will require creating some comparison
functions that don't exist by default. By the time you write and test
those, the select distinct method you actually used might be better.