Thread: 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)
> );