Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 15AF99FB41A for ; Tue, 10 Oct 2006 06:35:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 14348-06 for ; Tue, 10 Oct 2006 06:35:07 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.189]) by postgresql.org (Postfix) with ESMTP id 566B49FB425 for ; Tue, 10 Oct 2006 06:35:05 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id m18so238203nfc for ; Tue, 10 Oct 2006 02:35:04 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=UvQN5UNMyS4JrFq6HvWMIC1BR37+jJnwHMy147egQ6IjJPKAtljt+VHTAv1O9YLR7IWPusn4jqxGt6HVVd4pLvs0iHxcJVSPs/V4UX3o5JWrDluQ63cT3KTdHWvIk85KKCAEvvwHyhdsSxHvo4BYqHlAmAztzbjqDqJzyEg4z0E= Received: by 10.82.124.10 with SMTP id w10mr751928buc; Tue, 10 Oct 2006 02:35:04 -0700 (PDT) Received: by 10.82.183.4 with HTTP; Tue, 10 Oct 2006 02:35:03 -0700 (PDT) Message-ID: <621eda8a0610100235t25b6a5b7gf12ff063a7db278e@mail.gmail.com> Date: Tue, 10 Oct 2006 11:35:04 +0200 From: "Fourat Zouari" To: "Phillip Smith" Subject: Re: [NOVICE] Copying data from table to table (cloned tables) Cc: pgsql-novice@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: <009e01c6ec01$8631f060$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1249_2757434.1160472904029" References: <621eda8a0610091651oc78994fy2b358260f5c9b58a@mail.gmail.com> <009e01c6ec01$8631f060$9b0014ac@ITPhil> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.597 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/63 X-Sequence-Number: 23366 ------=_Part_1249_2757434.1160472904029 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline thanks for sharing, was very helpfull On 10/10/06, Phillip Smith wrote: > > Use the INTO keyword on a SELECT statement: > > SELECT * > > INTO coldqueues > > FROM hotqueues > > WHERE treatment_flag =3D 'DONE'; > > > > DELETE FROM hotqueues WHERE treatment_flag =3D 'DONE'; > > > > You will need to drop the coldqueues table first if you've already create= d > it as this will create it for you. I don't know if indexes and constraint= s > are copied from the source table, but you can always drop them afterwards= . > Don't forget to re-apply permissions etc to the new table if required. > > > > Another way if you don't want to drop the table would be to add the > keyword TEMP, copy to temporary table to a file, then copy that file back= to > the proper coldqueues table: > > SELECT * > > INTO *TEMP* coldqueues_temp > > FROM hotqueues > > WHERE treatment_flag =3D 'DONE'; > > > > COPY coldqueues_temp TO '/tmp/coldqueues.sql'; > > COPY coldqueues FROM '/tmp/coldqueues.sql'; > > > > DELETE FROM hotqueues WHERE treatment_flag =3D 'DONE'; > > > > This will prevent you having to delete the table in the first place =96 > which you won't want to do on future archives. Once you close the session > you have open, the "coldqueues_temp" table will automatically be dropped = for > you. > > > > Hope this helps, > > -p > > > > -----Original Message----- > *From:* pgsql-novice-owner@postgresql.org [mailto: > pgsql-novice-owner@postgresql.org] *On Behalf Of *Fourat Zouari > *Sent:* Tuesday, 10 October 2006 09:52 > *To:* pgsql-novice@postgresql.org; pgsql-admin@postgresql.org > *Subject:* [NOVICE] Copying data from table to table (cloned tables) > > > > Hello all, > Any one could suggest the best way to copy data from table to table in th= e > same db, the reason why am seeking for this is that the first table is > becoming very big, and old data has no reason why to stay there, so i > created a cloned table but without indexes and constraints (lighter) and = now > i would like to copy 'some' data from first to second table (copied data = is > data older than 3 months, i have a timestamp column). > > In other way, i have a table called 'hotqueues' where i store fresh > messages queued, once messages are treated, they stay in 'hotqueues' but > with a flag indicating that their arent queued for treatment.. > so in this way, data will rest there forever, slowing down any searches i= n > that table, the solution was to copy old messages to another table called > 'coldqueues' that has the same structure as 'hotqueues' but lighter (with= out > constraints and indexes). > How to copy these data with 100% data-loose free. > > Thanks for any help you can provide. > > ********************Confidentiality and Privilege > Notice******************** > > The material contained in this message is privileged and confidential to > the addressee. If you are not the addressee indicated in this message or > responsible for delivery of the message to such person, you may not copy = or > deliver this message to anyone, and you should destroy it and kindly noti= fy > the sender by reply email. > > Information in this message that does not relate to the official business > of Weatherbeeta must be treated as neither given nor endorsed by > Weatherbeeta. Weatherbeeta, its employees, contractors or associates shal= l > not be liable for direct, indirect or consequential loss arising from > transmission of this message or any attachments > > ------=_Part_1249_2757434.1160472904029 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline thanks for sharing, was very helpfull

On 10/10/06, Phillip Smith <phillip.smith@weatherbeeta.co= m.au > wrote:

Use the INTO keyword on a SELECT statement:

SELECT  &= nbsp;   *

INTO  &nb= sp;     coldqueues

FROM  &nb= sp;     hotqueues

WHERE   &= nbsp;   treatment_flag =3D 'DONE';

 

DELETE FROM hotqueu= es WHERE treatment_flag =3D 'DONE';

 

You will need to drop the coldqueu= es table first if you've already created it as this will create it for you. I don't know if indexes and constraints are copied from the source table, but you c= an always drop them afterwards. Don't forget to re-apply permissions etc to the new table if required.

 

Another way if you don't want to drop the table would be to add the keyword TEMP, copy to temporary table to= a file, then copy that file back to the proper coldqueues table:

SELECT  &= nbsp;   *

INTO TEMP&nb= sp;  coldqueues_temp

FROM  &nb= sp;     hotqueues

WHERE treatment_fla= g =3D 'DONE';

 

COPY coldqueues_tem= p TO '/tmp/coldqueues.sql';

COPY coldqueues FRO= M '/tmp/coldqueues.sql';

 

DELETE FROM hotqueu= es WHERE treatment_flag =3D 'DONE';

 

This will prevent you having to de= lete the table in the first place =96 which you won't want to do on future archives. Once you close the session you have open, the "coldqueues_temp" table will automatically be dropped for you.

 

Hope this helps,

-p

 

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-o= wner@postgresql.org] On Behalf Of Fourat Zouari
Sent:
Tuesday, 10 October 2006 09:52

To: pgsql-novice@postgresql.org; pgsql-admin@postgresql.org
Subject: [NOVICE] Copying = data from table to table (cloned tables)

<= span style=3D"font-size: 12pt;"> 

<= span style=3D"font-size: 12pt;">Hello all,
Any one could suggest the best way to copy data from table to table in the = same db, the reason why am seeking for this is that the first table is becoming = very big, and old data has no reason why to stay there, so i created a cloned ta= ble but without indexes and constraints (lighter) and now i would like to copy 'some' data from first to second table (copied data is data older than 3 months, i have a timestamp column).

In other way, i have a table called 'hotqueues' where i store fresh message= s queued, once messages are treated, they stay in 'hotqueues' but with a flag indicating that their arent queued for treatment..
so in this way, data will rest there forever, slowing down any searches in = that table, the solution was to copy old messages to another table called 'coldqueues' that has the same structure as 'hotqueues' but lighter (withou= t constraints and indexes).
How to copy these data with 100% data-loose free.

Thanks for any help you can provide.


*******************Confidentiality and Privilege Notice*****************= **

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbee= ta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of thi= s message or any attachments


------=_Part_1249_2757434.1160472904029--