Postgres Pro
Downloads
Home   >   mailing lists

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

How do I drop something that might not exist?

From
"Rob Richardson"
Date:
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

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

From
"Leif B. Kristensen"
Date:
On Thursday 19. March 2009, Rob Richardson wrote:
>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;

I don't know if it applies to your particular problem, but the standard
SQL if-then-else construct is:

CASE WHEN foo THEN bar ELSE baz;

HTH,
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/

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

From
David Saracini
Date:
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