pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to split a table?
11+ messages / 8 participants
[nested] [flat]
* How to split a table?
@ 2006-10-17 07:39 Felix Zhang <felix.zhang.2005@gmail.com>
2006-10-17 08:34 ` Re: How to split a table? Andreas Kretschmer <akretschmer@spamfence.net>
2006-10-17 09:26 ` Re: [NOVICE] How to split a table? Christoph Frick <frick@sc-networks.com>
0 siblings, 2 replies; 11+ messages in thread
From: Felix Zhang @ 2006-10-17 07:39 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org; pgsql-novice@postgresql.org
Hi,
I want to split a table to 2 small tables. The 1st one contains 60% records
which are randomly selected from the source table.
How to do it?
Regards,
Felix
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to split a table?
2006-10-17 07:39 How to split a table? Felix Zhang <felix.zhang.2005@gmail.com>
@ 2006-10-17 08:34 ` Andreas Kretschmer <akretschmer@spamfence.net>
2006-10-17 08:53 ` Re: [SQL] How to split a table? Gregory S. Williamson <gsw@globexplorer.com>
2006-10-17 09:07 ` Re: [NOVICE] How to split a table? Felix Zhang <felix.zhang.2005@gmail.com>
2006-10-17 09:09 ` Re: [GENERAL] How to split a table? Achilleas Mantzios <achill@matrix.gatewaynet.com>
1 sibling, 3 replies; 11+ messages in thread
From: Andreas Kretschmer @ 2006-10-17 08:34 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql; pgsql-novice@postgresql.org
Felix Zhang <felix.zhang.2005@gmail.com> schrieb:
> Hi,
>
> I want to split a table to 2 small tables. The 1st one contains 60% records
> which are randomly selected from the source table.
> How to do it?
Why do you want to do this?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] How to split a table?
2006-10-17 07:39 How to split a table? Felix Zhang <felix.zhang.2005@gmail.com>
2006-10-17 08:34 ` Re: How to split a table? Andreas Kretschmer <akretschmer@spamfence.net>
@ 2006-10-17 08:53 ` Gregory S. Williamson <gsw@globexplorer.com>
2006-10-17 09:12 ` Re: [SQL] [GENERAL] How to split a table? A. Kretschmer <andreas.kretschmer@schollglas.com>
2 siblings, 1 reply; 11+ messages in thread
From: Gregory S. Williamson @ 2006-10-17 08:53 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; pgsql-general@postgresql.org; pgsql-sql; pgsql-novice@postgresql.org
Perhaps something like:
CREATE TABLE foo2 AS SELECT * FROM foo WHERE (rand() <= 0.60);
?
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: pgsql-sql-owner@postgresql.org on behalf of Andreas Kretschmer
Sent: Tue 10/17/2006 1:34 AM
To: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; pgsql-novice@postgresql.org
Cc:
Subject: Re: [SQL] [GENERAL] How to split a table?
Felix Zhang <felix.zhang.2005@gmail.com> schrieb:
> Hi,
>
> I want to split a table to 2 small tables. The 1st one contains 60% records
> which are randomly selected from the source table.
> How to do it?
Why do you want to do this?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
-------------------------------------------------------
Click link below if it is SPAM gsw@globexplorer.com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4534936b271274356172766&user=gs...;
!DSPAM:4534936b271274356172766!
-------------------------------------------------------
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] [GENERAL] How to split a table?
2006-10-17 07:39 How to split a table? Felix Zhang <felix.zhang.2005@gmail.com>
2006-10-17 08:34 ` Re: How to split a table? Andreas Kretschmer <akretschmer@spamfence.net>
2006-10-17 08:53 ` Re: [SQL] How to split a table? Gregory S. Williamson <gsw@globexplorer.com>
@ 2006-10-17 09:12 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-10-17 09:19 ` Re: [SQL] [GENERAL] How to split a table? Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 1 reply; 11+ messages in thread
From: A. Kretschmer @ 2006-10-17 09:12 UTC (permalink / raw)
To: pgsql-sql; pgsql-novice@postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] [GENERAL] How to split a table?
2006-10-17 07:39 How to split a table? Felix Zhang <felix.zhang.2005@gmail.com>
2006-10-17 08:34 ` Re: How to split a table? Andreas Kretschmer <akretschmer@spamfence.net>
2006-10-17 08:53 ` Re: [SQL] How to split a table? Gregory S. Williamson <gsw@globexplorer.com>
2006-10-17 09:12 ` Re: [SQL] [GENERAL] How to split a table? A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2006-10-17 09:19 ` Gregory S. Williamson <gsw@globexplorer.com>
2006-10-17 10:51 ` Re: [SQL] [GENERAL] How to split a table? Jorge Godoy <jgodoy@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Gregory S. Williamson @ 2006-10-17 09:19 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-sql; pgsql-novice@postgresql.org
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=gs...;
!DSPAM:45349cb0275391789821027!
-------------------------------------------------------
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] [GENERAL] How to split a table?
2006-10-17 07:39 How to split a table? Felix Zhang <felix.zhang.2005@gmail.com>
2006-10-17 08:34 ` Re: How to split a table? Andreas Kretschmer <akretschmer@spamfence.net>
2006-10-17 08:53 ` Re: [SQL] How to split a table? Gregory S. Williamson <gsw@globexplorer.com>
2006-10-17 09:12 ` Re: [SQL] [GENERAL] How to split a table? A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-10-17 09:19 ` Re: [SQL] [GENERAL] How to split a table? Gregory S. Williamson <gsw@globexplorer.com>
@ 2006-10-17 10:51 ` Jorge Godoy <jgodoy@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jorge Godoy @ 2006-10-17 10:51 UTC (permalink / raw)
To: Gregory S. Williamson <gsw@globexplorer.com>; +Cc: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-sql; pgsql-novice@postgresql.org
"Gregory S. Williamson" <gsw@globexplorer.com> writes:
> 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 ...
If he's asking that 60% of the contents of the original table be randomly
selected this won't work. He'll have to count how many rows the original
table has, then loop randomly selecting rows until he has reached 60% of that
total. Otherwise he might end up with something completely different from
what he wants.
On the other hand if he wants rows whose randomness factor at the time they
were looked at was bigger than 0.6 then he can use that rand() trick.
--
Jorge Godoy <jgodoy@gmail.com>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [NOVICE] How to split a table?
2006-10-17 07:39 How to split a table? Felix Zhang <felix.zhang.2005@gmail.com>
2006-10-17 08:34 ` Re: How to split a table? Andreas Kretschmer <akretschmer@spamfence.net>
@ 2006-10-17 09:07 ` Felix Zhang <felix.zhang.2005@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: Felix Zhang @ 2006-10-17 09:07 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-general@postgresql.org; pgsql-sql; pgsql-novice@postgresql.org
to do some statistics analysis.
2006/10/17, Andreas Kretschmer <akretschmer@spamfence.net>:
>
> Felix Zhang <felix.zhang.2005@gmail.com> schrieb:
>
> > Hi,
> >
> > I want to split a table to 2 small tables. The 1st one contains 60%
> records
> > which are randomly selected from the source table.
> > How to do it?
>
> Why do you want to do this?
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknow)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [GENERAL] How to split a table?
2006-10-17 07:39 How to split a table? Felix Zhang <felix.zhang.2005@gmail.com>
2006-10-17 08:34 ` Re: How to split a table? Andreas Kretschmer <akretschmer@spamfence.net>
@ 2006-10-17 09:09 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2 siblings, 0 replies; 11+ messages in thread
From: Achilleas Mantzios @ 2006-10-17 09:09 UTC (permalink / raw)
To: pgsql-sql
Óôéò Ôñßôç 17 Ïêôþâñéïò 2006 11:34, ï/ç Andreas Kretschmer Ýãñáøå:
> Felix Zhang <felix.zhang.2005@gmail.com> schrieb:
> > Hi,
> >
> > I want to split a table to 2 small tables. The 1st one contains 60%
> > records which are randomly selected from the source table.
> > How to do it?
>
> Why do you want to do this?
In any case, you could write a program in perl and insert into the second
table rows from the first table, using a techique of generarting random
double precision numbers in a predetermined range (lets say 0 to 2^31-1), and
then converting this number to your "range" like this:
let your number be r.
let your table's cardinality be N.
then let R=(N*r/(2^31))+1
select a source row from your table like:
select * from srctable order by pk offset <R>,
and then insert this row to your desttable.
>
>
> Andreas
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [NOVICE] How to split a table?
2006-10-17 07:39 How to split a table? Felix Zhang <felix.zhang.2005@gmail.com>
@ 2006-10-17 09:26 ` Christoph Frick <frick@sc-networks.com>
1 sibling, 0 replies; 11+ messages in thread
From: Christoph Frick @ 2006-10-17 09:26 UTC (permalink / raw)
To: Felix Zhang <felix.zhang.2005@gmail.com>; +Cc: pgsql-sql; pgsql-general@postgresql.org; pgsql-novice@postgresql.org
On Tue, Oct 17, 2006 at 03:39:21PM +0800, Felix Zhang wrote:
> I want to split a table to 2 small tables. The 1st one contains 60%
> records which are randomly selected from the source table. How to do
> it?
i do my A/B-Group splitting usually by &1 the serial of the table.
assuming, that there are no irregularities in the process of creating
your entries, this should give a 50/50 split, which is reuseable for
future things and there never is a intersection of the two groups.
--
cu
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [NOVICE] How to split a table?
@ 2006-10-17 10:54 Obe, Regina <robe.dnd@cityofboston.gov>
0 siblings, 0 replies; 11+ messages in thread
From: Obe, Regina @ 2006-10-17 10:54 UTC (permalink / raw)
To: Felix Zhang <felix.zhang.2005@gmail.com>; pgsql-sql; pgsql-general@postgresql.org; pgsql-novice@postgresql.org
I would do
select * into mynewtable from myoldtable ORDER by random() LIMIT 15000
where 15000 in this case is <your table row count>*.6
If you want to create another table with 40% of the remaining data then
something like
select * into mynewtable2 from myoldtable where myoldtable.primarykey
NOT IN(select primarykey from mynewtable)
]
In this case primarykey you would replace with the primary key field of
your table.
________________________________
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Felix Zhang
Sent: Tuesday, October 17, 2006 3:39 AM
To: pgsql-sql@postgresql.org; pgsql-general@postgresql.org;
pgsql-novice@postgresql.org
Subject: [NOVICE] How to split a table?
Hi,
I want to split a table to 2 small tables. The 1st one contains 60%
records which are randomly selected from the source table.
How to do it?
Regards,
Felix
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally
privileged and/or exempt from disclosure pursuant to Massachusetts
law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and
delete the material from any computer.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Fwd: Re: [GENERAL] How to split a table?
@ 2006-10-17 11:08 Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 11+ messages in thread
From: Achilleas Mantzios @ 2006-10-17 11:08 UTC (permalink / raw)
To: pgsql-sql
I forgot to mention that you would have to maintain a counter of each inserted
row and stop when you reach 60% of N (where N the cardinality of your source
table).
---------- ÐñïùèçìÝíï ÌÞíõìá ----------
Subject: Re: [SQL] [GENERAL] How to split a table?
Date: Ôñßôç 17 Ïêôþâñéïò 2006 12:09
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Óôéò Ôñßôç 17 Ïêôþâñéïò 2006 11:34, ï/ç Andreas Kretschmer Ýãñáøå:
> Felix Zhang <felix.zhang.2005@gmail.com> schrieb:
> > Hi,
> >
> > I want to split a table to 2 small tables. The 1st one contains 60%
> > records which are randomly selected from the source table.
> > How to do it?
>
> Why do you want to do this?
In any case, you could write a program in perl and insert into the second
table rows from the first table, using a techique of generarting random
double precision numbers in a predetermined range (lets say 0 to 2^31-1), and
then converting this number to your "range" like this:
let your number be r.
let your table's cardinality be N.
then let R=(N*r/(2^31))+1
select a source row from your table like:
select * from srctable order by pk offset <R>,
and then insert this row to your desttable.
> Andreas
--
Achilleas Mantzios
-------------------------------------------------------
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-10-17 11:08 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-17 07:39 How to split a table? Felix Zhang <felix.zhang.2005@gmail.com>
2006-10-17 08:34 ` Andreas Kretschmer <akretschmer@spamfence.net>
2006-10-17 08:53 ` Gregory S. Williamson <gsw@globexplorer.com>
2006-10-17 09:12 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-10-17 09:19 ` Gregory S. Williamson <gsw@globexplorer.com>
2006-10-17 10:51 ` Jorge Godoy <jgodoy@gmail.com>
2006-10-17 09:07 ` Felix Zhang <felix.zhang.2005@gmail.com>
2006-10-17 09:09 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2006-10-17 09:26 ` Christoph Frick <frick@sc-networks.com>
2006-10-17 10:54 Re: [NOVICE] How to split a table? Obe, Regina <robe.dnd@cityofboston.gov>
2006-10-17 11:08 Fwd: Re: [GENERAL] How to split a table? Achilleas Mantzios <achill@matrix.gatewaynet.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