thanks for sharing, was very helpfull
On 10/10/06, Phillip Smith <phillip(dot)smith(at)weatherbeeta(dot)com(dot)au> wrote:
>
> Use the INTO keyword on a SELECT statement:
>
> SELECT *
>
> INTO coldqueues
>
> FROM hotqueues
>
> WHERE treatment_flag = 'DONE';
>
>
>
> DELETE FROM hotqueues WHERE treatment_flag = 'DONE';
>
>
>
> You will need to drop the coldqueues 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 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 = 'DONE';
>
>
>
> COPY coldqueues_temp TO '/tmp/coldqueues.sql';
>
> COPY coldqueues FROM '/tmp/coldqueues.sql';
>
>
>
> DELETE FROM hotqueues WHERE treatment_flag = 'DONE';
>
>
>
> This will prevent you having to delete the table in the first place –
> 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(at)postgresql(dot)org [mailto:
> pgsql-novice-owner(at)postgresql(dot)org] *On Behalf Of *Fourat Zouari
> *Sent:* Tuesday, 10 October 2006 09:52
> *To:* pgsql-novice(at)postgresql(dot)org; pgsql-admin(at)postgresql(dot)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 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 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 in
> that table, the solution was to copy old messages to another table called
> 'coldqueues' that has the same structure as 'hotqueues' but lighter (without
> 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
> Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall
> not be liable for direct, indirect or consequential loss arising from
> transmission of this message or any attachments
>
>