Re: contracting tables

>>>>> "Jeff" == Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com> writes:

Jeff> If you don't have a lot of indexes, sequences,
Jeff> referential integrity constraints etc. the easiest way
Jeff> would be:
Jeff> SELECT INTO table2 DISTINCT * FROM table1;
Jeff> DROP table1;
Jeff> ALTER TABLE table2 RENAME TO table1;
Jeff> Then recreate your other objects/constraints.

Jeff> If you want to do it in place, then:
Jeff> DELETE FROM table1
Jeff> WHERE EXISTS (
Jeff> SELECT * FROM table1 AS t1
Jeff> WHERE t1.key < table1.key
Jeff> );
Jeff> You will need an index on your "key" value, or this
Jeff> will take a long time on a large table.

Or maybe something like:

DELETE FROM table1
WHERE oid NOT IN (SELECT min(oid) FROM table1 GROUP BY key1, key2)

Untested, and I might be a little fuzzy on the syntax. This keeps
the lowest oid row for the given key1/key2 pair. Change that to *
to remove duplicates across all columns.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!