Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E6E469FA2A6; Tue, 10 Oct 2006 12:37:36 -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 32933-02; Tue, 10 Oct 2006 12:37:27 -0300 (ADT) 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 87EBC9FA220; Tue, 10 Oct 2006 12:37:27 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 7946C56431; Tue, 10 Oct 2006 10:37:26 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Tue, 10 Oct 2006 10:37:25 -0500 Date: Tue, 10 Oct 2006 10:37:25 -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: <20061010153724.GI72517@nasby.net> References: <621eda8a0610091651oc78994fy2b358260f5c9b58a@mail.gmail.com> <452AE2FF.1020409@presinet.com> <20061010152457.GG72517@nasby.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20061010152457.GG72517@nasby.net> 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::u20nW+D/VKo/mDxN:000000000000000 0000000000000000000000008F/o X-Hashcash: 1:20:061010:fourat@gmail.com::4E+4N3E/IhtDXchN:0290Q X-Hashcash: 1:20:061010:pgsql-novice@postgresql.org::Yvq5aMPg7tKqg92A:0000000000 0000000000000000000000000P6k X-Hashcash: 1:20:061010:pgsql-admin@postgresql.org::nZtzilRzpgfBqkPw:00000000000 0000000000000000000000001on3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.054 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200610/67 X-Sequence-Number: 23370 On Tue, Oct 10, 2006 at 10:24:57AM -0500, Jim C. Nasby wrote: > > 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; I forgot to mention you could also use a serializable transaction with your method... BEGIN ISOLATION LEVEL SERIALIZABLE; INSERT ... DELETE ... COMMIT; Just remember you'll need to deal with the possibility of a 'could not serialize' error. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)