From: | David Saracini <dsaracini(at)yahoo(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org, Rob Richardson <Rob(dot)Richardson(at)rad-con(dot)com> |
Subject: | Re: How do I drop something that might not exist? |
Date: | 2009-03-19 15:19:57 |
Message-ID: | 714387.5538.qm@web82904.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Saracini | 2009-03-19 15:38:25 | Re: Service start up error "The service name is invalid net helpmsg 2185" |
Previous Message | Leif B. Kristensen | 2009-03-19 14:40:12 | Re: How do I drop something that might not exist? |