Re: Query with Dynamic tables

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