Thread: Does this table exist?

Hi

I'm Alvaro Herrera, from Chile, South America, where I study CS
(mostly). I'm new to PostgreSQL and to SQL in general.

That's for my introduction.

Now, I'm building a database to hold customer data that needs to be
organized in a one-table-per-customer manner. No, I don't think there's
another way to do this, as these tables are potentially very big not in
the sense that I have too many customers, but in that I need to hold
very large amounts of data for each.

Now, the question is: Is there a way to know if a given table exist?
Besides doing a direct query to it, of course, as I think that would be
too much of a dinamite-fishing way of doing it.

Thanks in advance, and excuse my poor english if it offends you.

--
Alvaro Herrera (<alvherre[@]protecne.cl>)

Well, I'll answer your questions and help you a little too, or at least
try...

* Alvaro Herrera in "[GENERAL] Does this table exist?" dated 2000/11/02
* 17:44 wrote:

> Now, I'm building a database to hold customer data that needs to be
> organized in a one-table-per-customer manner. No, I don't think
> there's another way to do this, as these tables are potentially very
> big not in the sense that I have too many customers, but in that I
> need to hold very large amounts of data for each.

This probably means you need to rethink your database design, it's a
process called normalization, I won't go into it right now, but you
should check it out.

For example, a customer table with all your customers in it with a
separate call information table that links call info to customers.

> Now, the question is: Is there a way to know if a given table exist?
> Besides doing a direct query to it, of course, as I think that would
> be too much of a dinamite-fishing way of doing it.

You can get a query to do this if you go into psql -E, and use the \dt
command, actually with -E on the command line, all of the \ commands
will print out the queries they are using.

--
chalk slayer

Well, you can do
select * from pg_class where relname='<name>'
which will tell you if there's a view/table/index
with that name.

Stephan Szabo
sszabo@bigpanda.com

On Thu, 2 Nov 2000, Alvaro Herrera wrote:

> Hi
>
> I'm Alvaro Herrera, from Chile, South America, where I study CS
> (mostly). I'm new to PostgreSQL and to SQL in general.
>
> That's for my introduction.
>
> Now, I'm building a database to hold customer data that needs to be
> organized in a one-table-per-customer manner. No, I don't think there's
> another way to do this, as these tables are potentially very big not in
> the sense that I have too many customers, but in that I need to hold
> very large amounts of data for each.
>
> Now, the question is: Is there a way to know if a given table exist?
> Besides doing a direct query to it, of course, as I think that would be
> too much of a dinamite-fishing way of doing it.
>
> Thanks in advance, and excuse my poor english if it offends you.

> Now, the question is: Is there a way to know if a given table exist?
> Besides doing a direct query to it, of course, as I think that would be
> too much of a dinamite-fishing way of doing it.

I believe you want the pg_tables table (view?)  A quick and dirty way would
be :

select tablename from pg_tables where tablename = 'tablecheck' and if you
get a row, the table exists.  Alternatively you could select tablename from
pg_tables where tableowner != 'postgres' and get a list of all tables in the
current database that don't belong to the postgresql system.

Though I'm also fairly new to PostgreSQL, so this could also be 'dynamite
fishing'  ;)

Sincerely, Warren
-----
Warren Vanichuk,  Systems Engineer,
Street Light Productions Ltd.

 Alvaro,

> Now, I'm building a database to hold customer data that needs to be
> organized in a one-table-per-customer manner. No, I don't think there's
> another way to do this

 Maybe, there's  still a way to  implement this in a  theoretically correct
manner? In  fact, the  amount of  data (meaning  cardinal number,  I guess)
doesn't matter that much.
 Of course,  you can always query  data dictionary (system tables)  to find
out  whether particular  table exists,  but you're  not guaranteed  to have
these tables always the same, little bits may change between releases.
 You can split large tables `horizontally,' ie store relatively old data in
bigger, `archive' tables, and newer data in smaller, `live' ones.


--

 contaminated fish and microchips
  huge supertankers on Arabian trips
 oily propaganda from the leaders' lips
  all about the future
 there's people over here, people over there
  everybody's looking for a little more air
 crossing all the borders just to take their share
  planning for the future

 Rainbow, Difficult to Cure

Alvaro Herrera <alvherre@protecne.cl> writes:
> Now, the question is: Is there a way to know if a given table exist?

Several people pointed out that you can easily do that by fishing around
in the system catalogs, but that seems like a bad solution to me.  How
will you distinguish the tables that correspond to customers from the
other tables that are hanging around your database?

Seems like a better approach is to make an additional table that lists
all the customers and the names of their dedicated tables.  I won't
argue with you about whether you really need a dedicated table for the
bulky per-customer info, but surely there are a lot of smaller per-
customer items --- name, address, phone, that sort of thing --- that
are most reasonably kept in a master table.  Just add a column for the
name of the bulky table.

Also, you might want to think about making all these per-customer tables
be inheritance children of a single prototype table.

            regards, tom lane