Skip site navigation (1) Skip section navigation (2)

How do I drop something that might not exist?

From: "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: How do I drop something that might not exist?
Date: 2009-03-19 14:26:34
Message-ID: 04A6DB42D2BA534FAC77B90562A6A03DD62047@server.rad-con.local (view raw or flat)
Thread:
Lists: pgsql-novice
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

Responses

pgsql-novice by date

Next:From: kulmacet101Date: 2009-03-19 14:29:11
Subject: Re: Check if table exists
Previous:From: Andreas KretschmerDate: 2009-03-19 07:08:51
Subject: Re: Changing transform_null_equals (and other settings)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group