drop if exists

Ther attached patch is for comment. It implements "drop if exists" as
has recently been discussed. Illustration:

andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=# drop table if exists blurflx;
DROP TABLE
andrew=# create table blurflx ( x text);
CREATE TABLE
andrew=# drop table if exists blurflx;
DROP TABLE
andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=#

If the patch is acceptable I will work up some documentation and
regression tests.

cheers

andrew

On Nov 14, 2005, at 23:25 , Andrew Dunstan wrote:

>
> Ther attached patch is for comment. It implements "drop if exists"
> as has recently been discussed. Illustration:

Nifty! Thanks for working this up, Andrew!

> andrew=# drop table blurflx;
> ERROR: table "blurflx" does not exist
> andrew=# drop table if exists blurflx;
> DROP TABLE

I'm not sure what other DBMS' return in this situation (and kindly
ignore this suggestion if it's specified or otherwise determined),
but perhaps the output could be TABLE "blurlx" DOES NOT EXIST
(without the ERROR) or something more informative, rather than DROP
TABLE. It reminds me of the old behavior of outputting COMMIT even in
the case of transaction failure. I find the current behavior of
outputting ROLLBACK in the case of transaction failure more useful.

Michael Glaesemann
grzm myrealbox com

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> andrew=# drop table blurflx;
> ERROR: table "blurflx" does not exist
> andrew=# drop table if exists blurflx;
> DROP TABLE

If I read MySQL's documentation correctly, they emit a NOTE (equivalent
of a NOTICE message I suppose) when IF EXISTS does nothing because the
table doesn't exist. Seems like we should do likewise --- your second
example here seems actively misleading. That is, I'd rather see

andrew=# drop table if exists blurflx;
NOTICE: table "blurflx" does not exist, skipping
DROP TABLE

regards, tom lane

OK, now it looks like this:

andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=# drop table if exists blurflx;
NOTICE: table "blurflx" does not exist, skipping
DROP TABLE
andrew=# create table blurflx ( x text);
CREATE TABLE
andrew=# drop table if exists blurflx;
DROP TABLE
andrew=# drop table blurflx;
ERROR: table "blurflx" does not exist
andrew=#

revised patch attached.

cheers

andrew

Tom Lane wrote:

>Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>
>>andrew=# drop table blurflx;
>>ERROR: table "blurflx" does not exist
>>andrew=# drop table if exists blurflx;
>>DROP TABLE
>>
>>
>
>If I read MySQL's documentation correctly, they emit a NOTE (equivalent
>of a NOTICE message I suppose) when IF EXISTS does nothing because the
>table doesn't exist. Seems like we should do likewise --- your second
>example here seems actively misleading. That is, I'd rather see
>
>andrew=# drop table if exists blurflx;
>NOTICE: table "blurflx" does not exist, skipping
>DROP TABLE
>
>
> regards, tom lane
>
>
>

Removed from queue. Andrew is committing it.

---------------------------------------------------------------------------

Andrew Dunstan wrote:
>
> OK, now it looks like this:
>
> andrew=# drop table blurflx;
> ERROR: table "blurflx" does not exist
> andrew=# drop table if exists blurflx;
> NOTICE: table "blurflx" does not exist, skipping
> DROP TABLE
> andrew=# create table blurflx ( x text);
> CREATE TABLE
> andrew=# drop table if exists blurflx;
> DROP TABLE
> andrew=# drop table blurflx;
> ERROR: table "blurflx" does not exist
> andrew=#
>
> revised patch attached.
>
> cheers
>
> andrew
>
> Tom Lane wrote:
>
> >Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> >
> >
> >>andrew=# drop table blurflx;
> >>ERROR: table "blurflx" does not exist
> >>andrew=# drop table if exists blurflx;
> >>DROP TABLE
> >>
> >>
> >
> >If I read MySQL's documentation correctly, they emit a NOTE (equivalent
> >of a NOTICE message I suppose) when IF EXISTS does nothing because the
> >table doesn't exist. Seems like we should do likewise --- your second
> >example here seems actively misleading. That is, I'd rather see
> >
> >andrew=# drop table if exists blurflx;
> >NOTICE: table "blurflx" does not exist, skipping
> >DROP TABLE
> >
> >
> > regards, tom lane
> >
> >
> >

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Will we get this functionality for ALL objects?

Bruce Momjian wrote:
> Removed from queue. Andrew is committing it.
>
> ---------------------------------------------------------------------------
>
> Andrew Dunstan wrote:
>
>>OK, now it looks like this:
>>
>>andrew=# drop table blurflx;
>>ERROR: table "blurflx" does not exist
>>andrew=# drop table if exists blurflx;
>>NOTICE: table "blurflx" does not exist, skipping
>>DROP TABLE
>>andrew=# create table blurflx ( x text);
>>CREATE TABLE
>>andrew=# drop table if exists blurflx;
>>DROP TABLE
>>andrew=# drop table blurflx;
>>ERROR: table "blurflx" does not exist
>>andrew=#
>>
>>revised patch attached.
>>
>>cheers
>>
>>andrew
>>
>>Tom Lane wrote:
>>
>>
>>>Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>>>
>>>
>>>
>>>>andrew=# drop table blurflx;
>>>>ERROR: table "blurflx" does not exist
>>>>andrew=# drop table if exists blurflx;
>>>>DROP TABLE
>>>>
>>>>
>>>
>>>If I read MySQL's documentation correctly, they emit a NOTE (equivalent
>>>of a NOTICE message I suppose) when IF EXISTS does nothing because the
>>>table doesn't exist. Seems like we should do likewise --- your second
>>>example here seems actively misleading. That is, I'd rather see
>>>
>>>andrew=# drop table if exists blurflx;
>>>NOTICE: table "blurflx" does not exist, skipping
>>>DROP TABLE
>>>
>>>
>>> regards, tom lane
>>>
>>>
>>>
>
>
>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>
>

Christopher Kings-Lynne said:
> Will we get this functionality for ALL objects?
>

The patch does these: table, view, index, sequence, schema, type, domain,
and conversion. The reason is that these are all dealt with using the same
bit of the grammar, and the first 4 are pretty much completely done by the
same code.

I think anything else will have to be done individually, although the
pattern can be copied.

Perhaps we should take bids on what should/should not be covered.

cheers

andrew

On Nov 17, 2005, at 11:45 , Christopher Kings-Lynne wrote:

>> I think anything else will have to be done individually, although the
>> pattern can be copied.
>> Perhaps we should take bids on what should/should not be covered.
>
> Everything should be covered, otherwise it's just annoying for
> users...

Including objects that already have CREATE OR REPLACE?

Michael Glaesemann
grzm myrealbox com

> I think anything else will have to be done individually, although the
> pattern can be copied.
>
> Perhaps we should take bids on what should/should not be covered.

Everything should be covered, otherwise it's just annoying for users...

Chris

> Including objects that already have CREATE OR REPLACE?

I assume so - CREATE OR REPLACE doesn't drop things - only creates or
replaces them.

Chris

Christopher Kings-Lynne wrote:

>> I think anything else will have to be done individually, although the
>> pattern can be copied.
>>
>> Perhaps we should take bids on what should/should not be covered.
>
>
> Everything should be covered, otherwise it's just annoying for users...

Well, that's arguably more than I originally signed up for ;-) See

http://archives.postgresql.org/pgsql-hackers/2005-10/msg00632.php

There are currently DROP commends for the following 21 objects
(according to the docs).

AGGREGATE
CAST
CONVERSION
DATABASE
DOMAIN
FUNCTION
GROUP
INDEX
LANGUAGE
OPERATOR
OPERATOR CLASS
ROLE
RULE
SCHEMA
SEQUENCE
TABLE
TABLESPACE
TRIGGER
TYPE
USER
VIEW

If the consensus is to add this to all of them, then I propose to apply
the patch I have (with a slight fix for an oversight in the case of
domains, plus docs and tests) for the 8 cases and start working on the
remaining 13 as time permits. To be honest, I have not even looked at
those 13 cases.

One motivation for this, besides general utility, is to ease MySQL
migrations, btw, and AFAICT they only have three DROP commands and only
two of them (TABLE and DATABASE) have IF EXISTS - DROP INDEX does not
for some reason - probably because it is actually mapped to an ALTER
TABLE statement.

cheers

andrew

On Nov 17, 2005, at 11:51 , Christopher Kings-Lynne wrote:

>> Including objects that already have CREATE OR REPLACE?
>
> I assume so - CREATE OR REPLACE doesn't drop things - only creates
> or replaces them.

Of course. Silly me :)

Michael Glaesemann
grzm myrealbox com

> If the consensus is to add this to all of them, then I propose to apply
> the patch I have (with a slight fix for an oversight in the case of
> domains, plus docs and tests) for the 8 cases and start working on the
> remaining 13 as time permits. To be honest, I have not even looked at
> those 13 cases.

I agree. I can have a crack at the others as well. It's in my area of
ability I hope ;) (Except grammar janking)

Chris