Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAUH8im96301 for ; Fri, 30 Nov 2001 12:08:44 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fAUGdV119757 for pgsql-sql@postgresql.org; Fri, 30 Nov 2001 11:39:31 -0500 (EST) (envelope-from news) From: merlyn@stonehenge.com (Randal L. Schwartz) X-Newsgroups: comp.databases.postgresql.sql Subject: Re: contracting tables Date: 30 Nov 2001 08:39:30 -0800 Organization: Stonehenge Consulting Services; Portland, Oregon, USA Lines: 33 Message-ID: References: <20011130153854.46705.qmail@web20801.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: scrappy@hub.org User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/20.3 To: pgsql-sql@postgresql.org X-Archive-Number: 200112/20 X-Sequence-Number: 5843 >>>>> "Jeff" == Jeff Eckermann 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 Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!