pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
contracting tables
9+ messages / 7 participants
[nested] [flat]

* contracting tables
@ 2001-11-29 23:50  Peter T. Brown <peter@memeticsystems.com>
  0 siblings, 3 replies; 9+ messages in thread

From: Peter T. Brown @ 2001-11-29 23:50 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: contracting tables
@ 2001-11-30 07:53  Markus Bertheau <twanger@bluetwanger.de>
  parent: Peter T. Brown <peter@memeticsystems.com>
  2 siblings, 1 reply; 9+ messages in thread

From: Markus Bertheau @ 2001-11-30 07:53 UTC (permalink / raw)
  To: Peter T. Brown <peter@memeticsystems.com>; +Cc: pgsql-sql

On Fri, 2001-11-30 at 00:50, Peter T. Brown wrote:
> 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?

If I understood you right, one problem is to identify only one row of a
duplicate. There is a 'hidden' field oid in every table that is unique.
It goes like this: 

select oid, * from <yourtable>

So you could delete the rows by oid:

delete from <yourtable> where oid = <oid>

HTH

Markus Bertheau

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: contracting tables
@ 2001-11-30 08:51  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Markus Bertheau <twanger@bluetwanger.de>
  0 siblings, 0 replies; 9+ messages in thread

From: Christopher Kings-Lynne @ 2001-11-30 08:51 UTC (permalink / raw)
  To: Markus Bertheau <twanger@bluetwanger.de>; Peter T. Brown <peter@memeticsystems.com>; +Cc: pgsql-sql

This is complicated (and I haven't tested it) but it might work:

delete from <yourtable> where oid in (select (select oid from <yourtable>
where <field>=yt.<field>) from <yourtable> yt group by <field>)

Pretty crazy - don't know if it will work!

Chris

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Markus Bertheau
> Sent: Friday, 30 November 2001 3:53 PM
> To: Peter T. Brown
> Cc: postgres sql list
> Subject: Re: [SQL] contracting tables
>
>
> On Fri, 2001-11-30 at 00:50, Peter T. Brown wrote:
> > 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?
>
> If I understood you right, one problem is to identify only one row of a
> duplicate. There is a 'hidden' field oid in every table that is unique.
> It goes like this:
>
> select oid, * from <yourtable>
>
> So you could delete the rows by oid:
>
> delete from <yourtable> where oid = <oid>
>
> HTH
>
> Markus Bertheau
>
>
>




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: contracting tables
@ 2001-11-30 15:38  Jeff Eckermann <jeff_eckermann@yahoo.com>
  parent: Peter T. Brown <peter@memeticsystems.com>
  2 siblings, 2 replies; 9+ messages in thread

From: Jeff Eckermann @ 2001-11-30 15:38 UTC (permalink / raw)
  To: Peter T. Brown <peter@memeticsystems.com>; pgsql-sql

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" <peter@memeticsystems.com> 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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: contracting tables
@ 2001-11-30 15:45  Jeff Eckermann <jeff_eckermann@yahoo.com>
  parent: Peter T. Brown <peter@memeticsystems.com>
  2 siblings, 0 replies; 9+ messages in thread

From: Jeff Eckermann @ 2001-11-30 15:45 UTC (permalink / raw)
  To: Peter T. Brown <peter@memeticsystems.com>; pgsql-sql

Thinking about that some more:

SELECT INTO table2 DISTINCT * FROM table1;
TRUNCATE table1;
INSERT INTO table1 SELECT * FROM table2;
DROP TABLE2;

may be easiest, if you want to preserve table1, and
need distinct row values.

--- "Peter T. Brown" <peter@memeticsystems.com> 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



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: contracting tables
@ 2001-11-30 16:39  Randal L. Schwartz <merlyn@stonehenge.com>
  parent: Jeff Eckermann <jeff_eckermann@yahoo.com>
  1 sibling, 0 replies; 9+ messages in thread

From: merlyn@stonehenge.com @ 2001-11-30 16:39 UTC (permalink / raw)
  To: pgsql-sql

>>>>> "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!



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: contracting tables
@ 2001-11-30 17:46  Peter T. Brown <peter@memeticsystems.com>
  parent: Jeff Eckermann <jeff_eckermann@yahoo.com>
  1 sibling, 1 reply; 9+ messages in thread

From: Peter T. Brown @ 2001-11-30 17:46 UTC (permalink / raw)
  To: 'Jeff Eckermann' <jeff_eckermann@yahoo.com>; pgsql-sql

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" <peter@memeticsystems.com> 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




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: contracting tables
@ 2001-12-01 05:21  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Peter T. Brown <peter@memeticsystems.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 2001-12-01 05:21 UTC (permalink / raw)
  To: Peter T. Brown <peter@memeticsystems.com>; +Cc: 'Jeff Eckermann' <jeff_eckermann@yahoo.com>; pgsql-sql

"Peter T. Brown" <peter@memeticsystems.com> writes:
> I cannot seem to find the EXISTS keyword anywhere in the postgresql
> documentation.

Er ... um ... well ... there isn't any doco about EXISTS ... nor
related constructs such as IN.

I've attempted to rectify this in the 7.2-to-be docs, see
http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-subquery.html
It's a tad on the dry-and-technical side, for sure.  You might prefer
to consult an introductory SQL book or three.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: contracting tables
@ 2001-12-02 22:20  Roberto Mello <rmello@cc.usu.edu>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 9+ messages in thread

From: Roberto Mello @ 2001-12-02 22:20 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter T. Brown <peter@memeticsystems.com>; 'Jeff Eckermann' <jeff_eckermann@yahoo.com>; pgsql-sql

On Sat, Dec 01, 2001 at 12:21:16AM -0500, Tom Lane wrote:
> 
> Er ... um ... well ... there isn't any doco about EXISTS ... nor
> related constructs such as IN.

Is there a documentation TODO somewhere? I'm sure people would step up to
write/rectify more docs if they knew what needs to be written (myself
included).

-Roberto
-- 
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+
  Roberto Mello - Computer Science, USU - http://www.brasileiro.net 
       http://www.sdl.usu.edu - Space Dynamics Lab, Developer    
Je n'est suis pas très content avec cette classe de Calculus.



^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~2001-12-02 22:20 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-29 23:50 contracting tables Peter T. Brown <peter@memeticsystems.com>
2001-11-30 07:53 ` Markus Bertheau <twanger@bluetwanger.de>
2001-11-30 08:51   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-11-30 15:38 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2001-11-30 16:39   ` Randal L. Schwartz <merlyn@stonehenge.com>
2001-11-30 17:46   ` Peter T. Brown <peter@memeticsystems.com>
2001-12-01 05:21     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-02 22:20       ` Roberto Mello <rmello@cc.usu.edu>
2001-11-30 15:45 ` Jeff Eckermann <jeff_eckermann@yahoo.com>

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