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[(at)]protecne(dot)cl>)

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(at)bigpanda(dot)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.

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

> 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(at)protecne(dot)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