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

How can I check if table exists in DB?

From

Sergey Belikov

Date:

18 November 2003, 00:02:14

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.

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

From

Bruno LEVEQUE

Date:

18 November 2003, 06:41:21

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.leveque@net6d.com
http://www.net6d.com

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

From

Sergey Belikov

Date:

18 November 2003, 15:03:11

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

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

From

Stephan Szabo

Date:

18 November 2003, 15:35:43

On Tue, 18 Nov 2003, Sergey Belikov wrote:

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

Not really, but in 7.4, you'll have access to the standard
information_schema.tables which should basically stay the same barring
changes in its definition in the SQL spec.

pg_dumpall pg_restore

From

"Rob Sell"

Date:

19 November 2003, 15:04:16

Ok this should be simple but I am missing something here.

Yesterday I upgrade to 7.4 before I do that I run

$pg_dumpall > /tmp/pg_keep

When I try to restore off this file I get
pg_restore: [archiver] input file does not appear to be a valid archive

So I tar the file and it appears to be a valid archive now..
Now when I try to restore I get the error:

pg_restore: [tar archiver] could not find header for file toc.dat in tar
archive

What am I missing here? Or is my dump just not valid and I am out of luck.

Btw I did the dump with the 7.3 tools and now am using the 7.4 restore if
that makes any difference.

Rob

Re: pg_dumpall pg_restore

From

Stephan Szabo

Date:

19 November 2003, 16:06:01

On Wed, 19 Nov 2003, Rob Sell wrote:

> Ok this should be simple but I am missing something here.
>
> Yesterday I upgrade to 7.4 before I do that I run
>
> $pg_dumpall > /tmp/pg_keep

IIRC, dumpall uses the text output options of pg_dump which you restore
by passing to psql rather than using pg_restore (which is for the
custom/tar outputs I think).

Re: pg_dumpall pg_restore

From

Oliver Elphick

Date:

19 November 2003, 16:15:34

On Wed, 2003-11-19 at 16:02, Rob Sell wrote:
> Ok this should be simple but I am missing something here.
>
> Yesterday I upgrade to 7.4 before I do that I run
>
> $pg_dumpall > /tmp/pg_keep
>
> When I try to restore off this file I get
> pg_restore: [archiver] input file does not appear to be a valid archive

You just dumped to a text file.  You have to use --format=t to get a tar
archive.

Use
   psql -e -d template1 -f /tmp/pg_keep
to restore from your text dump.

> So I tar the file and it appears to be a valid archive now..
> Now when I try to restore I get the error:
>
> pg_restore: [tar archiver] could not find header for file toc.dat in tar
> archive

You can't turn a text dump into a tar dump like that.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "To show forth thy lovingkindness in the morning, and
      thy faithfulness every night."     Psalms 92:2