Thread: Query with Dynamic tables

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


I know this is possible, because i've done it with postgresql before!

Thanks

On 10/4/05 3:18 PM, "solarsail" <solarsail@gmail.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

You could try using table inheritance.  For example you would create a table
called

Mytable and have all your other tables inherit from it.  Something along the
form

CREATE TABLE mytable
(
  myfield1 int4,
  myfield2
)

CREATE TABLE mytable001
(
)
INHERITS (mytable)

And so on

When you want to insert then you insert into mytable00x

When you want to query all the tables then you do

SELECT myfield1, myfield2
FROM mytable





-----Original Message-----
From: solarsail [mailto:solarsail@gmail.com]
Sent: Tuesday, October 04, 2005 3:18 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Query with Dynamic tables


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


I know this is possible, because i've done it with postgresql before!

Thanks

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org