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

From: Sergey Belikov <belikov(at)bnl(dot)gov>
To: Bruno LEVEQUE <bruno(dot)leveque(at)net6d(dot)com>
Cc: NOVICE PSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: How can I check if table exists in DB?
Date: 2003-11-18 16:02:09
Message-ID: 3FBA4281.9040901@bnl.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Stephan Szabo 2003-11-18 16:34:23 Re: How can I check if table exists in DB?
Previous Message Bruno LEVEQUE 2003-11-18 07:36:54 Re: How can I check if table exists in DB?