Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 519A89FA5EA; Mon, 9 Oct 2006 21:02:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 66424-04; Tue, 10 Oct 2006 00:02:14 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id DA3DB9FB20C; Mon, 9 Oct 2006 21:02:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from presinet.com (mail.presinet.com [209.53.156.1]) by svr4.postgresql.org (Postfix) with ESMTP id DBE285AF02A; Tue, 10 Oct 2006 00:02:12 +0000 (GMT) Received: from [10.10.1.204] ([10.10.1.204]) by presinet.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 9 Oct 2006 16:59:41 -0700 Message-ID: <452AE2FF.1020409@presinet.com> Date: Mon, 09 Oct 2006 17:02:07 -0700 From: Bricklen Anderson User-Agent: Thunderbird 1.5.0.7 (X11/20060922) MIME-Version: 1.0 To: Fourat Zouari Cc: pgsql-novice@postgresql.org, pgsql-admin@postgresql.org Subject: Re: Copying data from table to table (cloned tables) References: <621eda8a0610091651oc78994fy2b358260f5c9b58a@mail.gmail.com> In-Reply-To: <621eda8a0610091651oc78994fy2b358260f5c9b58a@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 09 Oct 2006 23:59:41.0765 (UTC) FILETIME=[FC884F50:01C6EBFE] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200610/58 X-Sequence-Number: 23361 Fourat Zouari wrote: > 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. If you just want to copy the data across to the other table: begin; insert into table2 select * from table1 where ; commit; if you also want to remove that same data from table1: begin; insert into table2 select * from table1 where ; delete from table1 where ; commit;