Skip site navigation (1) Skip section navigation (2)

Re: How can I check if table exists in DB?

From: Bruno LEVEQUE <bruno(dot)leveque(at)net6d(dot)com>
To: Sergey Belikov <belikov(at)bnl(dot)gov>
Cc: NOVICE PSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: How can I check if table exists in DB?
Date: 2003-11-18 07:36:54
Message-ID: 3FB9CC16.7000409@net6d.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,

If you use a query like

 select * from pg_tables where schemaname='public';

you can see all yours tables.



Bruno

Sergey Belikov wrote:

> Dear PSQL experts,
> how can I check if some table exists in my database or not? I tried 
> the simplest approach:
> PGConn *conn=PQconnectdb("database='mydb'");
> PQexec(conn, "BEGIN");
> ...
> PGresult res=PQexec(conn,"SELECT id FROM mytable");
> if(PQresultStatus(res)==PGRES_FATAL_ERROR && 
> strstr(PQresultErrorMessage(res),"Relation \"mytable\" does not 
> exist") // mytable does not exist in mydb
> {
>    res=PQexec(conn,"CREATE TABLE mytable (id int, name text)");
> }
> ....
>
> Funny things happened if mytable did not exist: all  my queries after  
> PQexec(conn,"SELECT id FROM mytable") returned result status 
> PGRES_FATAL_ERROR, all my attempts to import large objects failed: 
> psql reported that it was unable to open large object with loid # 
> 12345 (but it had created that new large object!), and so on. After 
> multiple attempts I figured out that the only way to run my program 
> smoothly is to restart transaction after the test of the existence:
> PGresult res=PQexec(conn,"SELECT id FROM mytable");
> if(PQresultStatus(res)==PGRES_FATAL_ERROR && 
> strstr(PQresultErrorMessage(res),"Relation \"mytable\" does not 
> exist") // mytable does not exist in mydb
> {
>    PQexec(conn, "BEGIN");
>    res=PQexec(conn,"CREATE TABLE mytable (id int, name text)");
> }
> ....
> But it discarded all that I did in between first PQexec(conn, 
> "BEGIN"); and PGresult res=PQexec(conn,"SELECT id FROM mytable");. 
> Finally I was forced to check the existence of all necessary tables at 
> the beginning of the program, to create nonexistent ones, and only 
> then to start my transaction. Too ugly to be right way to solve this 
> problem. Has PSQL some function or macro that permits to do such check 
> without destruction of my transaction?
> Thank you, Sergey.
>

-- 
Bruno LEVEQUE
System Engineer
SARL NET6D
bruno(dot)leveque(at)net6d(dot)com
http://www.net6d.com



In response to

Responses

pgsql-novice by date

Next:From: Sergey BelikovDate: 2003-11-18 16:02:09
Subject: Re: How can I check if table exists in DB?
Previous:From: Sergey BelikovDate: 2003-11-18 01:01:05
Subject: How can I check if table exists in DB?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group