pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Fourat Zouari <fourat@gmail.com>
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>
Cc: pgsql-novice@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: Re: [NOVICE] Copying data from table to table (cloned tables)
Date: Tue, 10 Oct 2006 11:35:04 +0200
Message-ID: <621eda8a0610100235t25b6a5b7gf12ff063a7db278e@mail.gmail.com> (raw)
In-Reply-To: <009e01c6ec01$8631f060$9b0014ac@ITPhil>
References: <621eda8a0610091651oc78994fy2b358260f5c9b58a@mail.gmail.com>
	<009e01c6ec01$8631f060$9b0014ac@ITPhil>

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

view thread (13+ messages)  latest in thread

Message-ID: <621eda8a0610100235t25b6a5b7gf12ff063a7db278e@mail.gmail.com>
Permalink:  ../621eda8a0610100235t25b6a5b7gf12ff063a7db278e@mail.gmail.com/
Also on:    postgresql.org/message-id/621eda8a0610100235t25b6a5b7gf12ff063a7db278e@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: fourat@gmail.com, phillip.smith@weatherbeeta.com.au, pgsql-novice@postgresql.org
  Subject: Re: [NOVICE] Copying data from table to table (cloned tables)
  In-Reply-To: <621eda8a0610100235t25b6a5b7gf12ff063a7db278e@mail.gmail.com>
  References: <621eda8a0610091651oc78994fy2b358260f5c9b58a@mail.gmail.com> <009e01c6ec01$8631f060$9b0014ac@ITPhil> <621eda8a0610100235t25b6a5b7gf12ff063a7db278e@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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