Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id CB0C79FA1A7; Tue, 10 Oct 2006 12:25:10 -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 39579-09; Tue, 10 Oct 2006 15:24:59 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id A77819F9EF5; Tue, 10 Oct 2006 12:24:59 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id CE48056435; Tue, 10 Oct 2006 10:24:58 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Tue, 10 Oct 2006 10:24:57 -0500 Date: Tue, 10 Oct 2006 10:24:57 -0500 From: "Jim C. Nasby" To: Bricklen Anderson Cc: Fourat Zouari , pgsql-novice@postgresql.org, pgsql-admin@postgresql.org Subject: Re: Copying data from table to table (cloned tables) Message-ID: <20061010152457.GG72517@nasby.net> References: <621eda8a0610091651oc78994fy2b358260f5c9b58a@mail.gmail.com> <452AE2FF.1020409@presinet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <452AE2FF.1020409@presinet.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:061010:banderson@presinet.com::OrMXbxDBH2wCDF3f:000000000000000 0000000000000000000000001rGq X-Hashcash: 1:20:061010:fourat@gmail.com::5kkE0jIr3Jl/LhqB:00Y4z X-Hashcash: 1:20:061010:pgsql-novice@postgresql.org::OlZXw3KsvMJGrwWe:0000000000 0000000000000000000000002DzP X-Hashcash: 1:20:061010:pgsql-admin@postgresql.org::/sbZrd8hUVV51TOd:00000000000 0000000000000000000000002njt 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/66 X-Sequence-Number: 23369 On Mon, Oct 09, 2006 at 05:02:07PM -0700, Bricklen Anderson wrote: > 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; You need to be careful with this method. For what the OP wants to do it would probably work, but not always. The problem is that in some scenarios, won't necessarily return the same set of rows. Starting in 8.2 you'll be able to do something like INSERT INTO table2 DELET FROM table1 WHERE ... RETURNING *; The RETURNING * will return all the data that the command deleted. In older versions, your best bet is to store the data you're moving in a temporary table, and then use that to delete the exact rows. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)