From: | Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> |
---|---|
To: | solarsail <solarsail(at)gmail(dot)com>, <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Query with Dynamic tables |
Date: | 2005-10-04 19:58:41 |
Message-ID: | BF685731.FD00%sdavis2@mail.nih.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 10/4/05 3:18 PM, "solarsail" <solarsail(at)gmail(dot)com> wrote:
> [ i posted this originally to the pgsql-sql list, sorry for cross posting ]
>
> I have a large number of tables with a common naming convention
>
> mytable001, mytable002, mytable003 ... mytable00n
>
> I would like to do a query across all of the tables, however I do not know
> all of the tables before hand, and I do not want to ( cant ) manually
> generate a query like
>
> select * from mytable001, mytable002, mytable003
>
> I have a query that returns the names of the tables I want to query:
>
> select tablename from pg_tables where tablename like 'mytable%'
>
>
> We use the table as a logging repository. It can get very large 250
> 000+ records. Because of the large number of records that we have in
> the table we found it was much faster to perform inserts on a smaller
> table. Our current system rolls the tables over every 12 hours or
> so, creating a new table with the following behavior:
>
> CREATE TABLE mytable_temp {...}
>
> ALTER TABLE mytable RENAME TO mytable_NNN;
> ALTER TABLE mytable_temp RENAME TO mytable;
>
> I want to join the mytable_NNN tables together in order to perform
> queries against my huge set of data to generate some reports. I'm
> probably going to create a temporary table with a few indexes to make
> the reports run faster... however I need to join the tables all
> together first.
You could look at using UNION along with select into.
Create newtable (
....
);
Insert into newtable
Select * from mytable_001
Union
Select * from mytable_002
Union
Select * from mytable_003;
Sean
From | Date | Subject | |
---|---|---|---|
Next Message | Obe, Regina DND\MIS | 2005-10-04 20:28:59 | Re: Query with Dynamic tables |
Previous Message | Jaromír Kamler | 2005-10-04 19:38:49 | how i can state time of create table?? |