Thanks for this Mark.
This seems like it will work well for TABLES and VIEWS. However I have
also other categories such as USER, GROUP and TYPE. For these I cant
seem to find out where they are in the information_schema in postgres.
There is no CREATE GROUP statement in the SQL standard, so this is
probably why it is not in the information_schema.
The CREATE USER and CREATE TYPE statements are PostgreSQL extensions. So
these may not be in the information_schema for this reason.
Has anyone had to deal with this before.
Thanks,
Richie.
-----Original Message-----
From: mark reid [mailto:pgsql@markreid.org]
Sent: 26 July 2005 18:23
To: Walsh, Richard (Richard)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] dropping non-existent tables
Hello,
You can use the information_schema.* tables, which are part of the SQL
standard (and thus not proprietary).
-Mark.
Walsh, Richard (Richard) wrote:
> Hi,
> I have a problem in that I need to drop non-existent tables in a DDL
> script. This is in order that the script can re-build a database
> schema if the tables already exist. However, in Postgres this is
> proving to be a problem because if the table does not exist then the
> DDL execution will stop once it gets an error. I know that I can
> create a custom function that will check the relevant postGres table
> to see if the table already exists, but I don't want to do this as I
> would like to keep the DDL as non-proprietary as possible. Anyone any
> ideas on a work-around for this problem that does not involve creating
> a custom function.
> thanks,
> Richie.