X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D55A1DA01B for ; Tue, 4 Oct 2005 15:40:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03459-10 for ; Tue, 4 Oct 2005 18:40:53 +0000 (GMT) Received: from qproxy.gmail.com (qproxy.gmail.com [72.14.204.192]) by svr1.postgresql.org (Postfix) with ESMTP id EEB22DA063 for ; Tue, 4 Oct 2005 15:40:53 -0300 (ADT) Received: by qproxy.gmail.com with SMTP id v28so759621qbe for ; Tue, 04 Oct 2005 11:40:54 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=JgdKrH++zZ7g2rnwFaHlsgIisVgHA6P+EVZQpBd2Az0YuWM7b3b4gTMeXPtpIMiFEvu3iKJWe8+c8YUI1OGmKXKtY53Aczzihi4W5o9lbLFhgGRyMWUhKauBP7tNsYGzMCHQGWWImmM0cl4Kgthm179DhFkju+feXd83Gi69uY8= Received: by 10.65.116.13 with SMTP id t13mr3276034qbm; Tue, 04 Oct 2005 11:40:54 -0700 (PDT) Received: by 10.65.158.12 with HTTP; Tue, 4 Oct 2005 11:40:54 -0700 (PDT) Message-ID: Date: Tue, 4 Oct 2005 14:40:54 -0400 From: solarsail Reply-To: solarsail To: Tom Lane Subject: Re: using pg_tables and tablename in queries Cc: pgsql-sql@postgresql.org In-Reply-To: <9420.1128445291@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_18429_19147674.1128451254069" References: <9420.1128445291@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.006 required=5 tests=[AWL=0.093, HTML_30_40=0.056, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024, RCVD_IN_BL_SPAMCOP_NET=1.832] X-Spam-Level: ** X-Archive-Number: 200510/7 X-Sequence-Number: 22860 ------=_Part_18429_19147674.1128451254069 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 w= e 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 wit= h 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 perfor= m 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 ru= n faster... however I need to join the tables all together first. On 10/4/05, Tom Lane wrote: > > solarsail 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 > ------=_Part_18429_19147674.1128451254069 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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:

   =20 CREATE TABLE mytable_temp {...}

   =20 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 mytable= 001, 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 'my= table%'

This looks to me like a situation in which you should rethin= k 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.

   &nbs= p;            &= nbsp;       regards, tom lane

------=_Part_18429_19147674.1128451254069--