Re: [NOVICE] Copying data from table to table (cloned tables) - Mailing list pgsql-admin

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@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 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