Thread: Check for table existence

Check for table existence

From
Michael Knudsen
Date:
Hi group

First, let me say that I am new to postgres. I used MySQL before, but..
ehm..

I am trying to do a small program (in C), and I have stumbled on to some
problems:
1. How can I check if a given table exists?2. How can I check if a given existing table has the right layout? (Correct
column names and types)

Currently, I check using something like:
select * from giventable limit 0;

but that only helps checking if the table exists and if the number of
columns is correct. I'm sure, there must be a smoother way to do this, but I
cannot seem to find it. Anyone out there with good ideas?

M.
-- 
Rumour is information distilled so finely that it can filter through
anything.
-- (Terry Pratchett, Feet of Clay)


Re: Check for table existence

From
Alexander Steinert
Date:
> First, let me say that I am new to postgres. I used MySQL before, but..
> ehm..

Welcome.

>     1. How can I check if a given table exists?
>     2. How can I check if a given existing table has the right
>     layout? (Correct column names and types)

http://archives2.us.postgresql.org/pgsql-sql/2001-07/msg00319.php
and
http://archives2.us.postgresql.org/pgsql-sql/2001-07/msg00403.php
will show you the way to go.

Stony


Re: Check for table existence

From
Haller Christoph
Date:
Hi Michael, 
> 
> 
>     1. How can I check if a given table exists?
>     2. How can I check if a given existing table has the right layout? (Correct
> column names and types)
> 
Try this 
create view my_tbldescription as 
select 
u.usename, t.typname AS tblname, 
a.attname, a.atttypid, n.typname AS atttypname, 
int4larger(a.attlen, a.atttypmod - 4) AS atttyplen, 
a.attnotnull, a.attnum 
from pg_user u, pg_type t, pg_attribute a, pg_type n 
where u.usesysid = t.typowner 
and t.typrelid = a.attrelid and t.typtype = 'c' and not (t.typname ~* 'pg_') 
and n.typelem = a.atttypid 
and substr(n.typname, 1, 1) = '_' 
and a.attnum > 0 ;

select * from my_tbldescription order by 1,2,8 ;

It should tell you all you want to know. 
Regards, Christoph 


Re: Check for table existence

From
Roberto Mello
Date:
On Wed, Nov 28, 2001 at 10:47:42PM +0100, Michael Knudsen wrote:
> Hi group
> 
> First, let me say that I am new to postgres. I used MySQL before, but..
> ehm..
> 
> I am trying to do a small program (in C), and I have stumbled on to some
> problems:
> 
>     1. How can I check if a given table exists?
>     2. How can I check if a given existing table has the right layout? (Correct
> column names and types)

There's a function to check for a table's existance in the PostgreSQL
Cookbook (http://www.brasileiro.net/postgres/)

-Roberto

-- 
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU -
http://www.brasileiro.net      http://www.sdl.usu.edu - Space Dynamics Lab, Developer    
 
Daddy, why doesn't this magnet pick up this floppy?