pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Copying data from table to table (cloned tables)
13+ messages / 10 participants
[nested] [flat]

* Copying data from table to table (cloned tables)
@ 2006-10-09 23:51  Fourat Zouari <fourat@gmail.com>
  0 siblings, 3 replies; 13+ messages in thread

From: Fourat Zouari @ 2006-10-09 23:51 UTC (permalink / raw)
  To: pgsql-novice@postgresql.org; pgsql-admin

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.

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Copying data from table to table (cloned tables)
@ 2006-10-10 00:02  Bricklen Anderson <banderson@presinet.com>
  parent: Fourat Zouari <fourat@gmail.com>
  2 siblings, 1 reply; 13+ messages in thread

From: Bricklen Anderson @ 2006-10-10 00:02 UTC (permalink / raw)
  To: Fourat Zouari <fourat@gmail.com>; +Cc: pgsql-novice@postgresql.org; pgsql-admin

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 <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;



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Copying data from table to table (cloned tables)
@ 2006-10-10 09:22  daq <daq@ugyvitelszolgaltato.hu>
  parent: Fourat Zouari <fourat@gmail.com>
  2 siblings, 0 replies; 13+ messages in thread

From: daq @ 2006-10-10 09:22 UTC (permalink / raw)
  To: Fourat Zouari <fourat@gmail.com>; +Cc: pgsql-novice@postgresql.org


FZ> Hello all,
FZ> Any one could suggest the best way to copy data from table to table in the
FZ> same db, the reason why am seeking for this is that the first table is
FZ> becoming very big, and old data has no reason why to stay there, so i
FZ> created a cloned table but without indexes and constraints (lighter) and now
FZ> i would like to copy 'some' data from first to second table (copied data is
FZ> data older than 3 months, i have a timestamp column).

FZ> In other way, i have a table called 'hotqueues' where i store fresh messages
FZ> queued, once messages are treated, they stay in 'hotqueues' but with a flag
FZ> indicating that their arent queued for treatment..
FZ> so in this way, data will rest there forever, slowing down any searches in
FZ> that table, the solution was to copy old messages to another table called
FZ> 'coldqueues' that has the same structure as 'hotqueues' but lighter (without
FZ> constraints and indexes).
FZ> How to copy these data with 100% data-loose free.

FZ> Thanks for any help you can provide.

A simple solution:

INSERT INTO coldqueues SELECT * FROM hotqueues where ...;

See documentation page: http://www.postgresql.org/docs/8.1/static/sql-insert.html

DAQ




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [NOVICE] Copying data from table to table (cloned tables)
@ 2006-10-10 09:35  Fourat Zouari <fourat@gmail.com>
  parent: Fourat Zouari <fourat@gmail.com>
  2 siblings, 0 replies; 13+ messages in thread

From: Fourat Zouari @ 2006-10-10 09:35 UTC (permalink / raw)
  To: Phillip Smith <phillip.smith@weatherbeeta.com.au>; +Cc: pgsql-novice@postgresql.org; pgsql-admin

thanks for sharing, was very helpfull

On 10/10/06, Phillip Smith <phillip.smith@weatherbeeta.com.au> wrote:
>
>  Use the INTO keyword on a SELECT statement:
>
> SELECT      *
>
> INTO        coldqueues
>
> FROM        hotqueues
>
> WHERE       treatment_flag = 'DONE';
>
>
>
> DELETE FROM hotqueues WHERE treatment_flag = 'DONE';
>
>
>
> You will need to drop the coldqueues table first if you've already created
> it as this will create it for you. I don't know if indexes and constraints
> are copied from the source table, but you can always drop them afterwards.
> Don't forget to re-apply permissions etc to the new table if required.
>
>
>
> Another way if you don't want to drop the table would be to add the
> keyword TEMP, copy to temporary table to a file, then copy that file back to
> the proper coldqueues table:
>
> SELECT      *
>
> INTO *TEMP*   coldqueues_temp
>
> FROM        hotqueues
>
> WHERE treatment_flag = 'DONE';
>
>
>
> COPY coldqueues_temp TO '/tmp/coldqueues.sql';
>
> COPY coldqueues FROM '/tmp/coldqueues.sql';
>
>
>
> DELETE FROM hotqueues WHERE treatment_flag = 'DONE';
>
>
>
> This will prevent you having to delete the table in the first place –
> which you won't want to do on future archives. Once you close the session
> you have open, the "coldqueues_temp" table will automatically be dropped for
> you.
>
>
>
> Hope this helps,
>
> -p
>
>
>
> -----Original Message-----
> *From:* pgsql-novice-owner@postgresql.org [mailto:
> pgsql-novice-owner@postgresql.org] *On Behalf Of *Fourat Zouari
> *Sent:* Tuesday, 10 October 2006 09:52
> *To:* pgsql-novice@postgresql.org; pgsql-admin@postgresql.org
> *Subject:* [NOVICE] Copying data from table to table (cloned tables)
>
>
>
> 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.
>
>  ********************Confidentiality and Privilege
> Notice********************
>
> The material contained in this message is privileged and confidential to
> the addressee. If you are not the addressee indicated in this message or
> responsible for delivery of the message to such person, you may not copy or
> deliver this message to anyone, and you should destroy it and kindly notify
> the sender by reply email.
>
> Information in this message that does not relate to the official business
> of Weatherbeeta must be treated as neither given nor endorsed by
> Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall
> not be liable for direct, indirect or consequential loss arising from
> transmission of this message or any attachments
>
>

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Copying data from table to table (cloned tables)
@ 2006-10-10 15:24  Jim C. Nasby <jim@nasby.net>
  parent: Bricklen Anderson <banderson@presinet.com>
  0 siblings, 2 replies; 13+ messages in thread

From: Jim C. Nasby @ 2006-10-10 15:24 UTC (permalink / raw)
  To: Bricklen Anderson <banderson@presinet.com>; +Cc: Fourat Zouari <fourat@gmail.com>; pgsql-novice@postgresql.org; pgsql-admin

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 <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;

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, <same criteria as above> 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)



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Copying data from table to table (cloned tables)
@ 2006-10-10 15:37  Jim C. Nasby <jim@nasby.net>
  parent: Jim C. Nasby <jim@nasby.net>
  1 sibling, 1 reply; 13+ messages in thread

From: Jim C. Nasby @ 2006-10-10 15:37 UTC (permalink / raw)
  To: Bricklen Anderson <banderson@presinet.com>; +Cc: Fourat Zouari <fourat@gmail.com>; pgsql-novice@postgresql.org; pgsql-admin

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)



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Copying data from table to table (cloned tables)
@ 2006-10-11 11:30  Fourat Zouari <fourat@gmail.com>
  parent: Jim C. Nasby <jim@nasby.net>
  0 siblings, 2 replies; 13+ messages in thread

From: Fourat Zouari @ 2006-10-11 11:30 UTC (permalink / raw)
  To: Jim C. Nasby <jim@nasby.net>; +Cc: Bricklen Anderson <banderson@presinet.com>; pgsql-novice@postgresql.org; pgsql-admin

what's the benefits using a serialized trans ?

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)
>

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [ADMIN] Copying data from table to table (cloned tables)
@ 2006-10-11 12:56  Brandon Aiken <BAiken@winemantech.com>
  parent: Fourat Zouari <fourat@gmail.com>
  1 sibling, 0 replies; 13+ messages in thread

From: Brandon Aiken @ 2006-10-11 12:56 UTC (permalink / raw)
  To: Jim C. Nasby <jim@nasby.net>; +Cc: pgsql-novice@postgresql.org

It makes it so that a transaction is highly isolated.

 

http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html

 

 

--

Brandon Aiken

CS/IT Systems Engineer

________________________________

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)

 

what's the benefits using a serialized trans ?

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)

 

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Copying data from table to table (cloned tables)
@ 2006-10-11 15:14  Bruno Wolff III <bruno@wolff.to>
  parent: Fourat Zouari <fourat@gmail.com>
  1 sibling, 0 replies; 13+ messages in thread

From: Bruno Wolff III @ 2006-10-11 15:14 UTC (permalink / raw)
  To: Fourat Zouari <fourat@gmail.com>; +Cc: Jim C. Nasby <jim@nasby.net>; Bricklen Anderson <banderson@presinet.com>; pgsql-novice@postgresql.org; pgsql-admin

On Wed, Oct 11, 2006 at 13:30:22 +0200,
  Fourat Zouari <fourat@gmail.com> wrote:
> what's the benefits using a serialized trans ?

All of the queries in the transaction see a consistant view of the database.
So that two identical select queries will return the same information
within a serialized transaction. (As long as the current transaction doesn't
make any changes between the two selects that would affect their output.)



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Copying data from table to table (cloned tables)
@ 2006-10-13 21:45  Aaron Bono <postgresql@aranya.com>
  parent: Jim C. Nasby <jim@nasby.net>
  1 sibling, 0 replies; 13+ messages in thread

From: Aaron Bono @ 2006-10-13 21:45 UTC (permalink / raw)
  To: Jim C. Nasby <jim@nasby.net>; +Cc: Bricklen Anderson <banderson@presinet.com>; Fourat Zouari <fourat@gmail.com>; pgsql-novice@postgresql.org; pgsql-admin

On 10/10/06, Jim C. Nasby <jim@nasby.net> wrote:
>
> 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 <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;
>
> 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, <same criteria as above> 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.


You may want to lock the table before doing the copy/delete - that would
take care of having a record added between the copy and the delete.

If you are doing this on a regular basis, you may want to consider adding an
insert/update trigger on the original table to put a copy into the secondary
table and then all you have to do is do a simple delete from the originating
table every so often.

-- 
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
==================================================================

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Copying data from table to table (cloned tables)
@ 2006-10-27 12:38  shaik mastan dilshad <dilshad_sm@rediffmail.com>
  0 siblings, 2 replies; 13+ messages in thread

From: shaik mastan dilshad @ 2006-10-27 12:38 UTC (permalink / raw)
  To: Jim C.Nasby <jim@nasby.net>; +Cc: Fourat Zouari <fourat@gmail.com>; pgsql-novice@postgresql.org, pgsql-admin@postgresql.org, "Bricklen Anderson" <banderson@presinet.com>

Hi all,

Can any one tell me,how to retirve the data which had deleted for an instance ,i mean how to rollback the previous.

Look forward to hearing from all of you.

Regards,
S.M.Dilshad.

^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: [ADMIN] Copying data from table to table (cloned tables)
@ 2006-10-30 12:41  Richard Broersma Jr <rabroersma@yahoo.com>
  parent: shaik mastan dilshad <dilshad_sm@rediffmail.com>
  1 sibling, 0 replies; 13+ messages in thread

From: Richard Broersma Jr @ 2006-10-30 12:41 UTC (permalink / raw)
  To: shaik mastan dilshad <dilshad_sm@rediffmail.com>; +Cc: pgsql-novice@postgresql.org

> Can any one tell me,how to retirve the data which had deleted for an instance ,i mean how to
> rollback the previous.

To get the functionality of a rollback you next to begin with a transaction:

begin transaction;

delete from foo;

--oops I deleted to much;

rollback;

Regards,

Richard Broersma Jr.



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Copying data from table to table (cloned tables)
@ 2006-10-30 15:25  Jim C. Nasby <decibel@decibel.org>
  parent: shaik mastan dilshad <dilshad_sm@rediffmail.com>
  1 sibling, 0 replies; 13+ messages in thread

From: Jim C. Nasby @ 2006-10-30 15:25 UTC (permalink / raw)
  To: shaik mastan dilshad <dilshad_sm@rediffmail.com>; +Cc: Jim C.Nasby <jim@nasby.net>; Fourat Zouari <fourat@gmail.com>; pgsql-novice@postgresql.org, pgsql-admin@postgresql.org, Bricklen Anderson <banderson@presinet.com>

On Fri, Oct 27, 2006 at 12:38:51PM -0000, shaik mastan dilshad wrote:
> Hi all,
> 
> Can any one tell me,how to retirve the data which had deleted for an instance ,i mean how to rollback the previous.
> 
> Look forward to hearing from all of you.

Please start a new thread instead of replying to an existing one.

You can only rollback an uncommitted transaction. Once you commit, the
changes are permanent.

Your only other option is to setup Point In Time Recovery (PITR), and
use it to restore a copy of your database to just before committing a
the transaction you want to back out. Of course, everything that's
happened in the database since then will also be gone.
-- 
Jim C. Nasby, Database Architect                decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



^ permalink  raw  reply  [nested|flat] 13+ messages in thread


end of thread, other threads:[~2006-10-30 15:25 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-09 23:51 Copying data from table to table (cloned tables) Fourat Zouari <fourat@gmail.com>
2006-10-10 00:02 ` Bricklen Anderson <banderson@presinet.com>
2006-10-10 15:24   ` Jim C. Nasby <jim@nasby.net>
2006-10-10 15:37     ` Jim C. Nasby <jim@nasby.net>
2006-10-11 11:30       ` Fourat Zouari <fourat@gmail.com>
2006-10-11 12:56         ` Brandon Aiken <BAiken@winemantech.com>
2006-10-11 15:14         ` Bruno Wolff III <bruno@wolff.to>
2006-10-13 21:45     ` Aaron Bono <postgresql@aranya.com>
2006-10-10 09:22 ` daq <daq@ugyvitelszolgaltato.hu>
2006-10-10 09:35 ` Fourat Zouari <fourat@gmail.com>
2006-10-27 12:38 Re: Copying data from table to table (cloned tables) shaik mastan dilshad <dilshad_sm@rediffmail.com>
2006-10-30 12:41 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-10-30 15:25 ` Jim C. Nasby <decibel@decibel.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox