Re: [SQL] [GENERAL] How to split a table?
Στις Τρίτη 17 Οκτώβριος 2006 11:34, ο/η Andreas Kretschmer έγραψε: > Felix Zhang <[EMAIL PROTECTED]> 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 , and then insert this row to your desttable. > > > Andreas -- Achilleas Mantzios ---(end of broadcast)--- TIP 6: explain analyze is your friend
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
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: [EMAIL PROTECTED] on behalf of A. Kretschmer Sent: Tue 10/17/2006 2:12 AM To: pgsql-sql@postgresql.org; [EMAIL PROTECTED] 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 [EMAIL PROTECTED] "https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45349cb0275391789821027&[EMAIL PROTECTED]&retrain=spam&template=history&history_page=1" !DSPAM:45349cb0275391789821027! --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] 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
[SQL] Any documatation about porting from Oracle to PostgreSQL
Hi all, I'm a newbie of PostgreSQL. I'm searching materials about porting from Oracle to PostgreSQL. Anyone can share with me some good documatations? Thanks and regards, Felix
Re: [SQL] [GENERAL] How to split a table?
Felix Zhang <[EMAIL PROTECTED]> 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
Re: [SQL] [GENERAL] Any documatation about porting from Oracle to PostgreSQL
Felix Zhang <[EMAIL PROTECTED]> schrieb: > Hi all, > > I'm a newbie of PostgreSQL. I'm searching materials about porting from Oracle > to PostgreSQL. > Anyone can share with me some good documatations? http://techdocs.postgresql.org/#convertfrom 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 5: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] How to split a table?
Perhaps something like: CREATE TABLE foo2 AS SELECT * FROM foo WHERE (rand() <= 0.60); ? HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Andreas Kretschmer Sent: Tue 10/17/2006 1:34 AM To: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; [EMAIL PROTECTED] Cc: Subject:Re: [SQL] [GENERAL] How to split a table? Felix Zhang <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] "https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4534936b271274356172766&[EMAIL PROTECTED]&retrain=spam&template=history&history_page=1" !DSPAM:4534936b271274356172766! --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [NOVICE] [GENERAL] How to split a table?
to do some statistics analysis. 2006/10/17, Andreas Kretschmer <[EMAIL PROTECTED]>: Felix Zhang <[EMAIL PROTECTED]> 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 completelyunintentional 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
Re: [SQL] [GENERAL] Any documatation about porting from Oracle to PostgreSQL
am Tue, dem 17.10.2006, um 10:44:52 +0200 mailte Thomas Kellerer folgendes: > On 17.10.2006 10:36 Andreas Kretschmer wrote: > > > >http://techdocs.postgresql.org/#convertfrom > > > > I just noticed that the link "Porting from Oracle PL/SQL" still points > to the 7.4 manuals. Shouldn't that be updated to point to the current > release? > > And the link "Ora2Pg - Oracle to PostgreSQL database schema converter" > > (http://techdocs.postgresql.org/redir.php?link=http://www.samse.fr/GPL/ora2pg/ora2pg.html) > > gives a 404 error. Sorry. 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] How to split a table?
"Gregory S. Williamson" <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend
Fwd: Re: [SQL] [GENERAL] How to split a table?
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 <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Στις Τρίτη 17 Οκτώβριος 2006 11:34, ο/η Andreas Kretschmer έγραψε: > Felix Zhang <[EMAIL PROTECTED]> 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 , and then insert this row to your desttable. > Andreas -- Achilleas Mantzios --- -- Achilleas Mantzios ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Assigning a timestamp without timezone to a timestamp
chrisj <[EMAIL PROTECTED]> writes:
> When I first saw your solution I thought it was logically going to do
> (notice the parentheses):
> select ('2006-07-13 09:20:00'::timestamp) at time zone 'EST5EDT';
> which does not help
Well, actually, that's exactly what it does. AT TIME ZONE is an
operator that converts timestamp without time zone to timestamp with
time zone (or vice versa). I guess you could easily get confused
here, but AT is not WITH.
> , cast(start_datetime as timestamp(0) without time zone)::timestamp at
> time zone B.timezone_ch
That's redundant --- you're casting the result of the cast to timestamp
(implicitly without time zone), then applying the AT TIME ZONE operator.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL][GENERAL] Any documatation about porting from Oracle to PostgreSQL
Felix, You might want to look at EnterpriseDB, which is PostgreSQL with Oracle compatibility extensions. www.enterprisedb.com LewisC --- Felix Zhang <[EMAIL PROTECTED]> wrote: > Hi all, > > I'm a newbie of PostgreSQL. I'm searching materials about porting > from > Oracle to PostgreSQL. > Anyone can share with me some good documatations? > > Thanks and regards, > Felix > --- Lewis R Cunningham ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/ EnterpriseDB: The Definitive Reference http://www.rampant-books.com/book_2007_1_enterprisedb.htm -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Assigning a timestamp without timezone to a timestamp
Hi Tom,
Thanks again, I did not appreciate the dual function of "AT TIME ZONE" when
the input is timestamptz then the function converts from one timezone to
another (not what I wanted),
but when the input is timestamp the function acts more like a cast than a
convert (exactly what I wanted)
I must disagree with your assertion about the redundancy of:
> , cast(start_datetime as timestamp(0) without time zone)::timestamp at
> time zone B.timezone_ch
what I am doing is taking a timestamptz, discarding its timezone, and then
casting it to another timezone
for example from 2006-10-03 09:00:00 NZST to 2006-10-03 09:00:00 EST5EDT
If I am missing a much easier way to accomplish this please let me know.
Tom Lane-2 wrote:
>
> chrisj <[EMAIL PROTECTED]> writes:
>> When I first saw your solution I thought it was logically going to do
>> (notice the parentheses):
>> select ('2006-07-13 09:20:00'::timestamp) at time zone 'EST5EDT';
>> which does not help
>
> Well, actually, that's exactly what it does. AT TIME ZONE is an
> operator that converts timestamp without time zone to timestamp with
> time zone (or vice versa). I guess you could easily get confused
> here, but AT is not WITH.
>
>> , cast(start_datetime as timestamp(0) without time zone)::timestamp at
>> time zone B.timezone_ch
>
> That's redundant --- you're casting the result of the cast to timestamp
> (implicitly without time zone), then applying the AT TIME ZONE operator.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
--
View this message in context:
http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6863766
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
