On Thu, Feb 03, 2005 at 23:04:57 -0200, Clodoaldo Pinto <clodoaldo.pinto@xxxxxxxxx> 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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
