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(dot)leveque(at)net6d(dot)com
http://www.net6d.com