How can I check if table exists in DB?

From: Sergey Belikov <belikov(at)bnl(dot)gov>
To: NOVICE PSQL <pgsql-novice(at)postgresql(dot)org>
Subject: How can I check if table exists in DB?
Date: 2003-11-18 01:01:05
Message-ID: 3FB96F51.7080005@bnl.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Bruno LEVEQUE 2003-11-18 07:36:54 Re: How can I check if table exists in DB?
Previous Message Sergey Belikov 2003-11-18 00:22:13 Re: Threads in PSQL