Re: drop if exists

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:

> Andrew Dunstan wrote:
>
| >
| > From time to time the suggestion crops up of allowing a DROP IF EXISTS
| > ... syntax. This seems not unreasonable, and I just spent a few minutes
| > looking at what might be involved.
>
> What about "CREATE IF NOT EXISTS" (CINE)? If we support DROP IF EXISTS
> (DIE), is the other one going to be supported too?
>
> How does this play with schemas? I assume DIE drops the table in any
> schema in the search path. What if there's more than one; drop the
> first one? CINE creates the schema in the first schema in the path,
> just like CREATE.
>
> Also, DIE does not need to lock the table afterwards because it won't
> exist, but CINE needs to keep a lock until transaction commit.

The "DROP IF EXISTS" is usually used in database creation scripts in
order to rebuild contents of schema. The parallel "CREATE IF EXISTS"
does not sound as useful feature.

DROP ... that table
CREATE ... that table

DROP ... that index
CREATE ... that index

etc.

It would also be MySQL compatible if DROP IF EXISTS were implemented.

Jari