Received: from mail.memeticsystems.com (flywheel.memeticsystems.com [64.85.80.85]) by postgresql.org (8.11.3/8.11.4) with SMTP id fAUHk6m97934 for ; Fri, 30 Nov 2001 12:46:06 -0500 (EST) (envelope-from peter@memeticsystems.com) Received: (qmail 22283 invoked from network); 30 Nov 2001 17:46:10 -0000 Received: from unknown (HELO PETER) (63.249.24.21) by flywheel.memeticsystems.com with SMTP; 30 Nov 2001 17:46:10 -0000 From: "Peter T. Brown" To: "'Jeff Eckermann'" , Subject: Re: contracting tables Date: Fri, 30 Nov 2001 09:46:09 -0800 Message-ID: <010a01c179c6$e5874ea0$7d00000a@PETER> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2911.0) In-reply-to: <20011130153854.46705.qmail@web20801.mail.yahoo.com> X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal X-Archive-Number: 200111/442 X-Sequence-Number: 5823 Thanks for the reply everyone! I cannot seem to find the EXISTS keyword anywhere in the postgresql documentation.. What does that do? And, generally, do you guys think it better/more efficient/etc to select into, rename, drop tables OR to do the complex single SQL to operate on the table directly? Thanks -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Jeff Eckermann Sent: Friday, November 30, 2001 7:39 AM To: Peter T. Brown; pgsql-sql@postgresql.org Subject: Re: [SQL] contracting tables 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html