Thread: 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.
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: > 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.
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/
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.