Обсуждение: Error dropping non-existent tables
Hi, I am a new PostgreSQL user and am not sure if this is the correct list for this question, please redirect me to the correct list, if needed? I recently starting using PostgreSQL 8.0.0-beta4 (Win32) and want to migrate some of the SQL scripts written for MySQL to PostgreSQL. I have the following SQL command, is there an equivalent version for PostgreSQL? "DROP TABLE IF EXISTS my_table CASCADE;" I am able to drop tables in PostgreSQL, but the problem arises when the table does not exist and I try to execute a "drop table" command. thanks in advance, Baldeep.
On Fri, Nov 19, 2004 at 21:38:33 -0800, Baldeep Hira <baldeephira@gmail.com> wrote: > > I am able to drop tables in PostgreSQL, but the problem arises when > the table does not exist and I try to execute a "drop table" command. The simplest fix is to do the drop table outside of a transaction (so that the error in the drop doesn't break the rest of your script). If that won't work for you then you can write a custom function that looks in the system catalog to see if the table exists before trying the drop.
Thanks Bruno. A silly followup question =) On Sat, 20 Nov 2004 10:51:13 -0600, Bruno Wolff III <bruno@wolff.to> wrote: > On Fri, Nov 19, 2004 at 21:38:33 -0800, > Baldeep Hira <baldeephira@gmail.com> wrote: > > > > I am able to drop tables in PostgreSQL, but the problem arises when > > the table does not exist and I try to execute a "drop table" command. > > The simplest fix is to do the drop table outside of a transaction (so > that the error in the drop doesn't break the rest of your script). > If that won't work for you then you can write a custom function that > looks in the system catalog to see if the table exists before trying > the drop. > How do I move the "DROP TABLE" commands into a separate transaction? I could move all the "DROP TABLE" commands into a separate sql-script file, but then I never know which of the tables are existing in the database, thus that script will break as well. When I execute a bunch of SQL commands from a script file, do all of them form a single transaction? Anyway, I can have multiple transactions from a single script file? Currently my sql-script file looks like this. DROP TABLE table1 CASCADE; DROP TABLE table2 CASCADE; DROP TABLE table3 CASCADE; DROP TABLE table4 CASCADE; CREATE TABLE table1 ( name VARCHAR(255) NOT NULL, id INTEGER NOT NULL ); CREATE TABLE table2 ( id INTEGER NOT NULL, type INTEGER NOT NULL ); CREATE TABLE table3 ( id INTEGER NOT NULL, p_id INTEGER NOT NULL, CONSTRAINT fk_table3 FOREIGN KEY (id) REFERENCES table2 (id) ); CREATE TABLE table4 ( id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, CONSTRAINT pk_table4 PRIMARY KEY (id), CONSTRAINT u_table4 UNIQUE (name) ); thanks, Baldeep.
On Sat, Nov 20, 2004 at 10:15:46 -0800, Baldeep Hira <baldeephira@gmail.com> wrote: > How do I move the "DROP TABLE" commands into a separate transaction? I > could move all the "DROP TABLE" commands into a separate sql-script > file, but then I never know which of the tables are existing in the > database, thus that script will break as well. When I execute a > bunch of SQL commands from a script file, do all of them form a single > transaction? Anyway, I can have multiple transactions from a single > script file? Just like you do below. You will get error messages for the failed drops, but everything will still work correctly. Were you actually having a problem other than seeing the error message? > > Currently my sql-script file looks like this. > > DROP TABLE table1 CASCADE; > DROP TABLE table2 CASCADE; > DROP TABLE table3 CASCADE; > DROP TABLE table4 CASCADE; > > CREATE TABLE table1 ( > name VARCHAR(255) NOT NULL, > id INTEGER NOT NULL > ); > > CREATE TABLE table2 ( > id INTEGER NOT NULL, > type INTEGER NOT NULL > ); > > CREATE TABLE table3 ( > id INTEGER NOT NULL, > p_id INTEGER NOT NULL, > CONSTRAINT fk_table3 FOREIGN KEY (id) > REFERENCES table2 (id) > ); > > CREATE TABLE table4 ( > id INTEGER NOT NULL, > name VARCHAR(255) NOT NULL, > CONSTRAINT pk_table4 PRIMARY KEY (id), > CONSTRAINT u_table4 UNIQUE (name) > );