Re: [ADMIN] Copying data from table to table (cloned tables)

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 10/10/06, Jim C. Nasby <jim@nasby.net> wrote:

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)