Received: from web20801.mail.yahoo.com (web20801.mail.yahoo.com [216.136.226.190]) by postgresql.org (8.11.3/8.11.4) with SMTP id fAUFcom92770 for ; Fri, 30 Nov 2001 10:38:50 -0500 (EST) (envelope-from jeff_eckermann@yahoo.com) Message-ID: <20011130153854.46705.qmail@web20801.mail.yahoo.com> Received: from [65.11.165.34] by web20801.mail.yahoo.com via HTTP; Fri, 30 Nov 2001 07:38:54 PST Date: Fri, 30 Nov 2001 07:38:54 -0800 (PST) From: Jeff Eckermann Subject: Re: contracting tables To: "Peter T. Brown" , pgsql-sql@postgresql.org In-Reply-To: <00e001c17930$aa8af8b0$7d00000a@PETER> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200111/437 X-Sequence-Number: 5818 If you don't have a lot of indexes, sequences, referential integrity constraints etc. the easiest way would be: SELECT INTO table2 DISTINCT * FROM table1; DROP table1; ALTER TABLE table2 RENAME TO table1; Then recreate your other objects/constraints. If you want to do it in place, then: DELETE FROM table1 WHERE EXISTS ( SELECT * FROM table1 AS t1 WHERE t1.key < table1.key ); You will need an index on your "key" value, or this will take a long time on a large table. --- "Peter T. Brown" wrote: > Hi-- > > I have a table with many records, some of which are > duplicates (there is no > unique constraints). How can I contract this table > to remove any of these > duplicate records? Like when using GROUP BY in a > select statement, except > that I want to just remove the extra entries from > this table directly... Any > ideas? > > > Thanks, > > Peter Brown > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly __________________________________________________ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1