From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Fourat Zouari Sent: Wednesday, October 11, 2006 7:30 AM To: Jim C. Nasby Cc: Bricklen Anderson; pgsql-novice@postgresql.org; pgsql-admin@postgresql.org Subject: Re: [NOVICE] [ADMIN] Copying data from table to table (cloned tables)
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 <some criteria>; > > commit; > > > > if you also want to remove that same data from table1: > > begin; > > insert into table2 select * from table1 where <some criteria>; > > delete from table1 where <same criteria as above>; > > 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)