Re: Moving data from one set of tables to another? - Mailing list pgsql-novice

On Thu, Sep 18, 2008 at 7:28 PM, Howard Eglowstein
<howard@yankeescientific.com> wrote:
> What confuses me is that I need to do the one select with all three tables
> and then do three inserts, no? The results is that the 150 fields I get back
> from the select have to be split into 3 groups of 50 fields each and then
> written into three tables.

You do the insert part of the command three times, once for each new
table, so three separate SQL statements.  The select remains basically
the same for all three, with only the column selection changing
(data_a.* when inserting into new_a, data_b.* when inserting into
new_b, etc.).  Just leave the ids the same as in the first set of
tables.  There isn't a need to change them in nearly every case.  If
you need to add a new ID column, you can do that as a serial column in
the new tables, but I would stick to the original IDs, if possible.

Sean

> What you're suggesting is that there is some statement which could do the
> select and the three inserts at once?
>
> Howard
>
> Sean Davis wrote:
>>
>> You might want to look at insert into ... select ...
>>
>> You should be able to do this with 1 query per new table (+ the
>> deletes, obviously).  For a few thousand records, I would expect that
>> the entire process might take a few seconds.
>>
>> Sean
>>
>
>> On Thu, Sep 18, 2008 at 6:39 PM, Howard Eglowstein
>> <howard@yankeescientific.com> wrote:
>>
>>>
>>> Somewhat empty, yes. The single set of 'data_' tables contains 3 years
>>> worth
>>> of data. I want to move 2 years worth out into the 'new_' tables. When
>>> I'm
>>> done, there will still be 1 year's worth of data left in the original
>>> table.
>>>
>>> Howard
>>>
>>> Carol Walter wrote:
>>>
>>>>
>>>> What do you want for your end product?  Are the old tables empty after
>>>> you
>>>> put the data into the new tables?
>>>>
>>>> Carol
>>>>
>>>> On Sep 18, 2008, at 3:02 PM, Howard Eglowstein wrote:
>>>>
>>>>
>>>>>
>>>>> I have three tables called 'data_a', 'data_b' and 'data_c' which each
>>>>> have 50 columns. One of the columns in each is 'id' and is used to keep
>>>>> track of which data in data_b and data_c corresponds to a row in
>>>>> data_a.  If
>>>>> I want to get all of the data in all 150 fields for this month (for
>>>>> example), I can get it with:
>>>>>
>>>>> select * from (data_a, data_b, data_c) where data_a.id=data_b.id AND
>>>>> data_a.id = data_c.id AND timestamp >= '2008-09-01 00:00:00' and
>>>>> timestamp
>>>>> <= '2008-09-30 23:59:59'
>>>>>
>>
>>
>>
>>>>>
>>>>> What I need to do is execute this search which might return several
>>>>> thousand rows and write the same structure into 'new_a', 'new_b' and
>>>>> 'new_c'. What i'm doing now in a C program is executing the search
>>>>> above.
>>>>> Then I execute:
>>>>>
>>>>> INSERT INTO data_a (timestamp, field1, field2 ...[imagine 50 of them])
>>>>> VALUES ('2008-09-01 00:00:00', 'ABC', 'DEF', ...);
>>>>> Get the ID that was assigned to this row since 'id' is a serial field
>>>>> and
>>>>> the number is assigned sequentially. Say it comes back as '1'.
>>>>> INSERT INTO data_b (id, field1, field2 ...[imagine 50 of them]) VALUES
>>>>> ('1', 'ABC', 'DEF', ...);
>>>>> INSERT INTO data_c (id, field1, field2 ...[imagine 50 of them]) VALUES
>>>>> ('1', 'ABC', 'DEF', ...);
>>>>>
>>>>> That moves a copy of the three rows of data form the three tables into
>>>>> the three separate new tables.
>>>>> From the original group of tables, the id for these rows was, let's
>>>>> say,
>>>>> '1234'. Then I execute:
>>>>>
>>>>> DELETE FROM data_a where id='1234';
>>>>> DELETE FROM data_b where id='1234';
>>>>> DELETE FROM data_c where id='1234';
>>>>>
>>>>> That deletes the old data.
>>>>>
>>>>> This works fine and gives me exactly what I wanted, but is there a
>>>>> better
>>>>> way? This is 7 SQL calls and it takes about 3 seconds per moved record
>>>>> on
>>>>> our Linux box.
>>>>>
>>>>> Any thoughts or suggestions would be appreciated.
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Howard
>>>>>
>>>>> --
>>>>> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
>>>>> To make changes to your subscription:
>>>>> http://www.postgresql.org/mailpref/pgsql-novice
>>>>>
>>>>
>>>> ------------------------------------------------------------------------
>>>>
>>>>
>>>> No virus found in this incoming message.
>>>> Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database:
>>>> 270.6.21/1678 - Release Date: 9/18/2008 9:01 AM
>>>>
>>>>
>>>>
>>>
>>> --
>>> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-novice
>>>
>>>
>>
>>  ------------------------------------------------------------------------
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - http://www.avg.com Version: 8.0.169 / Virus Database:
>> 270.6.21/1678 - Release Date: 9/18/2008 9:01 AM
>>
>>
>
>