Thread

1. Does this table exist?

   Alvaro Herrera <alvherre@protecne.cl> — 2000-11-02T20:44:48Z

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

2. Re: Does this table exist?

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-11-02T20:55:29Z

   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.
   
   
   

3. Re: Does this table exist?

   Ashley Clark <aclark@ghoti.org> — 2000-11-02T21:01:45Z

   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
   

4. Re: Does this table exist?

   Warren Vanichuk <pyber@street-light.com> — 2000-11-02T21:09:07Z

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

5. Re: Does this table exist?

   KuroiNeko <evpopkov@carrier.kiev.ua> — 2000-11-02T21:34:07Z

    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
   
   

6. Re: Does this table exist?

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-02T21:40:22Z

   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