Re: Moving data from one set of tables to another?

So you'd agree then that I'll need 7 SQL statements but that I could
stack the INSERT and the first SELECT if I wanted to? Cool. That's what
I ended up with in C code and it's working pretty well. I did some
indexing on the database and got the whole transaction down to about
150ms for the sequence. I guess that's as good as it's going to get.

Thanks for the explanation!

Howard

Sean Davis wrote:
> 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
>>>
>>>
>>>
> >
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com
> Version: 8.0.169 / Virus Database: 270.7.0/1679 - Release Date: 9/18/2008 5:03 PM
>
>