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

[David Saracini <dsaracini(at)yahoo(dot)com>]

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(dot)Richardson(at)rad-con(dot)com> wrote:

> From: Rob Richardson <Rob(dot)Richardson(at)rad-con(dot)com>
> Subject: [NOVICE] How do I drop something that might not exist?
> To: pgsql-novice(at)postgresql(dot)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