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?

thanx,
keith evans



--
"Every magnificent history begins with something small." Daisaku Ikeda
================================================== =====
Keith D. Evans
Joint Center for Earth Systems Technology/UMBC
(301) 614-6282 (M,Tu)
(410) 455-5751 (W,Th,F)
http://www.jcet.umbc.edu/bios/evanmain.html
================================================== ======

Any opinions expressed in this email are not those of
NASA, or the Goddard Space Flight Center, or the Joint
Center for Earth Systems Technology or the University
of Maryland Baltimore County.

================================================== ======



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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?
>
> thanx,
> keith evans
>
>
>
20,000 tables all with the same columns strikes me as being a bad table
design. The normal way to do this would be to have all 20,000 tables in
one table with an extra column specifying which group (original table)
they belong to. You might consider refactoring your database.

That being said, you might consider creating a table of the names of all
of these tables, sticking the query inside a function which takes the
table name as a parameter, and then select over the table name table
calling the function. I'm not sure if that'd work (I'm a newbie as
well), but it might work.

Brian


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Wednesday 31 May 2006 04:40 pm, "Keith D. Evans" <evans@umbc.edu> thus
communicated:
--> 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?
-->
--> thanx,
--> keith evans
-->

If I understand the issue correctly, it seems that you need to be generating
dynamic SQL statements. Look at the docs for the execute command. It gives
examples of how to do this.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> 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?

I am not sure if your tables partations were created using inheritance. But if they were, you
would only have to "select * from parenttable;" and it would pull all of the data from all of its
children. While this would work, I would expect that the query would run slow. Or at least as
fast as a series on UNION All to combine all table data.

Regards,

Richard

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Brian, Terry, Richard and Sean,

thanx for your help.

Each table is a satellite overpass and there are 254 overpasses for each
cycle and 157 cycles presently. I came in later for this is someone
else's design. I have to learn database, too, so I'm taking what code
was there and trying to implement it.

thanx,
keith


Brian Hurt wrote:

> 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?
>>
>> thanx,
>> keith evans
>>
>>
>>
> 20,000 tables all with the same columns strikes me as being a bad
> table design. The normal way to do this would be to have all 20,000
> tables in one table with an extra column specifying which group
> (original table) they belong to. You might consider refactoring your
> database.
>
> That being said, you might consider creating a table of the names of
> all of these tables, sticking the query inside a function which takes
> the table name as a parameter, and then select over the table name
> table calling the function. I'm not sure if that'd work (I'm a newbie
> as well), but it might work.
>
> Brian
>


--
"Every magnificent history begins with something small." Daisaku Ikeda
================================================== =====
Keith D. Evans
Joint Center for Earth Systems Technology/UMBC
(301) 614-6282 (M,Tu)
(410) 455-5751 (W,Th,F)
http://www.jcet.umbc.edu/bios/evanmain.html
================================================== ======

Any opinions expressed in this email are not those of
NASA, or the Goddard Space Flight Center, or the Joint
Center for Earth Systems Technology or the University
of Maryland Baltimore County.

================================================== ======



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend