pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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!



view thread (9+ messages)  latest in thread

Message-ID: <m1lmgow6v1.fsf@halfdome.holdit.com>
Permalink:  ../m1lmgow6v1.fsf@halfdome.holdit.com/
Also on:    postgresql.org/message-id/m1lmgow6v1.fsf@halfdome.holdit.com

 · 

reply

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