From: 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 14:40:54 -0400
Message-ID: <aa44f4a30510041140s1f552a78yfa44cb287144ad2b@mail.gmail.com> (raw)
In-Reply-To: <9420.1128445291@sss.pgh.pa.us>
References: <aa44f4a30510040930m510bbe8do4babd97b1b37c90d@mail.gmail.com>
<9420.1128445291@sss.pgh.pa.us>
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
>
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: solarsail@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: using pg_tables and tablename in queries
In-Reply-To: <aa44f4a30510041140s1f552a78yfa44cb287144ad2b@mail.gmail.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