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 82BBED9B69 for ; Tue, 4 Oct 2005 14:01:34 -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 71937-05 for ; Tue, 4 Oct 2005 17:01:32 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id C223ED7AAB for ; Tue, 4 Oct 2005 14:01:31 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j94H1VIT009421; Tue, 4 Oct 2005 13:01:31 -0400 (EDT) To: solarsail Cc: pgsql-sql@postgresql.org Subject: Re: using pg_tables and tablename in queries In-reply-to: References: Comments: In-reply-to solarsail message dated "Tue, 04 Oct 2005 12:30:55 -0400" Date: Tue, 04 Oct 2005 13:01:31 -0400 Message-ID: <9420.1128445291@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200510/6 X-Sequence-Number: 22859 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