Re: searching multiple tables

[Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>]

Keith D. Evans wrote:
> Hello novices,
>
> I hope some aren't novices so that the questions can be answered. :-)
>
> I want to search many tables for certain data. The tables all have 
> exactly the same columns. In searching the archives, I noticed that the 
> exact table name seemed to be required for the select ... from .. 
> command, but we are talking about 20,000 tables or more. Specifiying 
> each table would be extremely inconvenient. And we may want to search 
> all the tables. Some of the columns are time, latitude and longitude.  
> We want to be able to search on time and/or lat and lon. Time will be 
> unique, but the lats and lons may repeat. Based on these search 
> criteria, we will pull out other data (columns) from the table(s).
>
> I have been reading the documentation manuals for postgresql 7.4 and 
> browsing the archives, but have not found an answer to this question. 
> Can anyone help me?

Sounds like you need to rethink your schema.  Combine all 20,000 tables 
into one table with an additional column that contains the original 
table name.  Then, you can do a simple select on the one table.  If you 
need the original table structure, you can make 20,000 views, one for 
each of the original tables.

Sean