From: Jim Buttafuoco <jim@contactbda.com>
To: solarsail <solarsail@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: using pg_tables and tablename in queries
Date: Tue, 4 Oct 2005 15:45:07 -0400
Message-ID: <20051004194359.M25426@contactbda.com> (raw)
In-Reply-To: <aa44f4a30510041140s1f552a78yfa44cb287144ad2b@mail.gmail.com>
References: <aa44f4a30510040930m510bbe8do4babd97b1b37c90d@mail.gmail.com>
<9420.1128445291@sss.pgh.pa.us>
<aa44f4a30510041140s1f552a78yfa44cb287144ad2b@mail.gmail.com>
do you mean UNION ALL instead of JOIN, if you mean UNION ALL , I would go with a set returning function passing it
the necessary WHERE clause to be applied to all of your tables. You might be able to wrap the whole thing into a view
---------- Original Message -----------
From: solarsail <solarsail@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Sent: Tue, 4 Oct 2005 14:40:54 -0400
Subject: Re: [SQL] using pg_tables and tablename in queries
> The current behavior is by design.
>
> 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_back_datetime;
> ALTER TABLE mytable_temp RENAME TO mytable;
>
> I want to join the mytable_back_datetime 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.
>
> On 10/4/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > solarsail <solarsail@gmail.com> writes:
> > > 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%'
> >
> > This looks to me like a situation in which you should rethink your
> > data design. Those tables should all get merged into one big table,
> > adding one extra column that reflects what you had been using to
> > segregate the data into different tables.
> >
> > regards, tom lane
> >
------- End of Original Message -------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jim@contactbda.com, solarsail@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: using pg_tables and tablename in queries
In-Reply-To: <20051004194359.M25426@contactbda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox