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

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.leveque@net6d.com
http://www.net6d.com