How can I check if table exists in DB? - Mailing list pgsql-novice

From Sergey Belikov
Subject How can I check if table exists in DB?
Date
Msg-id 3FB96F51.7080005@bnl.gov
Whole thread Raw
Responses Re: How can I check if table exists in DB?
List pgsql-novice
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.

pgsql-novice by date:

Previous
From: Sergey Belikov
Date:
Subject: Re: Threads in PSQL
Next
From: Godshall Michael
Date:
Subject: creating/droping a table from a function stored in public vs a sc hema