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
Views: Raw Message | Whole Thread | Download mbox
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

Browse pgsql-novice by date

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