Re: How do I drop something that might not exist?

Hello,

Some drop stmts will allow you to include an IF EXISTS in them.

For example:

    DROP TABLE IF EXISTS <name> CASCADE;
    DROP SEQUENCE IF EXISTS <name>;
    DROP VIEW IF EXISTS <name> CASCADE;
    DROP INDEX IF EXISTS <name> CASCADE;

however, others (such as dropping a column) do not support such checking (at least that I have been able to find).

I hope this helps (or sorta answers your question).

David

--- On Thu, 3/19/09, Rob Richardson <Rob.Richardson@rad-con.com> wrote:

> From: Rob Richardson <Rob.Richardson@rad-con.com>
> Subject: [NOVICE] How do I drop something that might not exist?
> To: pgsql-novice@postgresql.org
> Date: Thursday, March 19, 2009, 9:26 AM
> Greetings!
>
> I am writing a set of SQL statements that will create
> tables that may be
> required for one of our test programs.  I would like to
> clean out any
> pre-existing versions of these tables and the sequences
> they use, but I
> don't know if those pre-existing things will actually
> exist.  If I issue
> a command like
>
> DROP SEQUENCE this_sequence_does_not_exist;
>
> then an exception is raised and no other commands in my SQL
> file will be
> executed.
>
> I tried using an if statement combined with the EXISTS()
> function:
>
> if exists(select 1 from this_sequence_does_not_exist) then
>     drop sequence this_sequence_does_not_exist;
> end if;
>
> but in a plain SQL window, the "if" statement
> does not seem to work.
> Even if it did, I would still get an exception when
> Postgresql tries to
> access the nonexistent sequence.
>
> The only thing that seems to have a chance of working is to
> create a
> function, and then inside that function used a
> BEGIN/EXCEPTION
> construct, and then drop the function after I'm
> finished with it.  But
> this seems to be an awfully roundabout way of doing this.
>
> What is the recommended way of doing this?
>
> Thank you very much.
>
> RobR