SQL for removing duplicates?

Поиск
Список
Период
Сортировка
От
Тема SQL for removing duplicates?
Дата
Msg-id 200606131611.k5DGBff21012@panix3.panix.com
обсуждение исходный текст
Ответы Re: SQL for removing duplicates?  (Brad Nicholson <bnichols@ca.afilias.info>)
Список pgsql-novice


Hi.  I'm stumped.  I have a large table (about 8.5M records), let's
call it t, whose columns include x and y.  I want to remove records
from this table so that any pair of values for these two fields appear
only once.  (This will get rid of about 15% of the records in t.)

One simple solution would be something like

  CREATE TABLE tmp AS SELECT DISTINCT ON ( x, y ) * FROM t;
  DROP TABLE t;
  ALTER TABLE tmp RENAME TO t;

This works, but it uses a lot of space.  I would prefer to simply cull
the unwanted records from t, but I just can't figure out the SQL for
it.  Any help with it would be *much* appreciated.

Thanks!

kj


В списке pgsql-novice по дате отправления:

Предыдущее
От: Oisin Glynn
Дата:
Сообщение: Re: Scheduled tasks
Следующее
От: Brad Nicholson
Дата:
Сообщение: Re: SQL for removing duplicates?