From: merlyn@stonehenge.com
To: pgsql-sql@postgresql.org
Subject: Re: contracting tables
Date: 30 Nov 2001 08:39:30 -0800
Message-ID: <m1lmgow6v1.fsf@halfdome.holdit.com> (raw)
References: <20011130153854.46705.qmail@web20801.mail.yahoo.com>
>>>>> "Jeff" == Jeff Eckermann <jeff_eckermann@yahoo.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@stonehenge.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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: merlyn@stonehenge.com
Subject: Re: contracting tables
In-Reply-To: <m1lmgow6v1.fsf@halfdome.holdit.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox