dropping non-existent tables
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.
Hello,
You can use the information_schema.* tables, which are part of the SQL
standard (and thus not proprietary).
-Mark.
Walsh, Richard (Richard) wrote:
Show quoted text
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.
On Tue, Jul 26, 2005 at 06:04:08PM +0200, Walsh, Richard (Richard) wrote:
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.
Are you executing the statements inside a transaction? If so then
you could execute the DROP statements in separate transactions (or
execute them outside of any transaction in autocommit mode). Also,
if you're using psql then make sure you don't have ON_ERROR_STOP set.
In PostgreSQL 8.1, psql will have an ON_ERROR_ROLLBACK setting that
uses savepoints to automatically roll back failed statements while
allowing the rest of the transaction to continue.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
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:
Show quoted text
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.
Import Notes
Resolved by subject fallback
Hi Michael,
I am executing these statements inside an SQL DDL script so I think they
all take place inside a single transaction. I am not using PSQL. I will
try what you suggest and see if executing the drop statements in a
separate tx will work.
Richie.
-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: 26 July 2005 23:09
To: Walsh, Richard (Richard)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] dropping non-existent tables
On Tue, Jul 26, 2005 at 06:04:08PM +0200, Walsh, Richard (Richard)
wrote:
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.
Are you executing the statements inside a transaction? If so then you
could execute the DROP statements in separate transactions (or execute
them outside of any transaction in autocommit mode). Also, if you're
using psql then make sure you don't have ON_ERROR_STOP set.
In PostgreSQL 8.1, psql will have an ON_ERROR_ROLLBACK setting that uses
savepoints to automatically roll back failed statements while allowing
the rest of the transaction to continue.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Import Notes
Resolved by subject fallback