Re: [SQL] [GENERAL] How to split a table?

A crude approach would be to add a column to the original table; then update that based on the rand() call:

update foo set i_am_a_60 = 1 where (rand() <= 0.60);
create table foo_60 as select * from foo where i_am_a_60 = 1;
create table foo_40 as select * from foo where i_am_a_60 <> 1;

The CASE condition might be usable as well but I haven't puzzled it out ...

G


-----Original Message-----
From:    pgsql-sql-owner@postgresql.org on behalf of A. Kretschmer
Sent:    Tue 10/17/2006 2:12 AM
To:    pgsql-sql@postgresql.org; pgsql-novice@postgresql.org
Cc:
Subject:    Re: [SQL] [GENERAL] How to split a table?

am  Tue, dem 17.10.2006, um  1:53:35 -0700 mailte Gregory S. Williamson folgendes:
> Perhaps something like:
>
> CREATE TABLE foo2 AS SELECT * FROM foo WHERE (rand() <= 0.60);

Then we have 2 tables: one with 100% data and one with around 60% ;-)
If the table contains a primary key you can delete simple the copied
records from the origin table.

(delete from origin where pk in (select pk from copy);


>
> -----Original Message-----

Please, no top-posting with fullquote below.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


-------------------------------------------------------
Click link below if it is SPAM gsw@globexplorer.com

"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45349cb0275391789821027&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:45349cb0275391789821027!
-------------------------------------------------------