Thread: drop table if exists

How can I duplicate the behavior of:

DROP TABLE IF EXISTS mytable;

CREATE TABLE mytable (
blah,
blah
);

INSERT INTO mytable
(blah)
VALUES
(blah);

in other words, so that I have a single sql file that restores the database
to a known state.

Doesn't work. I like wrapping up the entire file in a transaction so that if
I make a stupid syntax error or the like, I can just do a rollback. Because
of that, the transaction enters abort state. I suppose I can just stop using
transactions and use this method.

Just drop the table using "DROP TABLE mytable;" and ignore the error...
I'm sure there are fancy ways of doing it by accessing system tables, but
the above works for me.

On Tue, 3 Jul 2001, Jason Watkins wrote:

> How can I duplicate the behavior of:
>
> DROP TABLE IF EXISTS mytable;
>
> CREATE TABLE mytable (
> blah,
> blah
> );
>
> INSERT INTO mytable
> (blah)
> VALUES
> (blah);
>
> in other words, so that I have a single sql file that restores the database
> to a known state.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>

Alright, but what about when you're writing a long dump file with all these
sequences, functions not speaking about the tables themselves.
When I drop something I wanna make sure it out the database (but still
cannot empty the whole database before), so I have 

DROP TABLE ..., ..., ..., ...;
DROP FUNCTION ..., ..., ..., ...;
DROP SEQUENCE ..., ..., ..., ...;

If one of these were not there, the whole dump crashes and I need to restart
the process modifying the SQL file.

I'd LOVE to have a magic 'IF EXISTS' like in mySQL - make life easier
(Although I totally disrespect mySQL).
Has anyone a work around for this?

Thanks,
and yes, sorry for the late post, just looked you up guys.


Sincerely, 
Maxim MaletskyFounder, Chief DeveloperPHPBeginner.com (Where PHP Begins)maxim@phpbeginner.comwww.phpbeginner.com



-----Original Message-----
From: Philip Hallstrom [mailto:philip@adhesivemedia.com]
Sent: Wednesday, July 04, 2001 7:39 AM
To: Jason Watkins
Cc: pgsql-sql@postgresql.org
Subject: [SQL] Re: drop table if exists


Just drop the table using "DROP TABLE mytable;" and ignore the error...
I'm sure there are fancy ways of doing it by accessing system tables, but
the above works for me.

On Tue, 3 Jul 2001, Jason Watkins wrote:

> How can I duplicate the behavior of:
>
> DROP TABLE IF EXISTS mytable;
>
> CREATE TABLE mytable (
> blah,
> blah
> );
>
> INSERT INTO mytable
> (blah)
> VALUES
> (blah);
>
> in other words, so that I have a single sql file that restores the
database
> to a known state.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster