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

Hi Bruno!
Thank you very much for your help!!! It works very nicely!!!
Yet as I understand pg_tables is a system table. In some book I got
advice to not rely on a schema of system tables.
Is there any warranty that the field names will not be changed in
pg_tables table for at least 5 years?
Regards, Sergey.
Bruno LEVEQUE wrote:

> 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.
>>
>