Postgres Pro
Downloads
Home   >   mailing lists

Thread: drop table if exists

drop table if exists

From
Kevin Coyner
Date:
In MySQL, when I create a new table from a .sql file, I usually start
with:

DROP TABLE IF EXISTS sometable;
CREATE TABLE sometable(


This doesn't work in Postgres and I haven't been able to find the
equivalent in the docs.  If I try to drop a table that doesn't exist, I
get an error message, so having that modifier "... if exists ..." is
nice.

Is there an equivalent to "IF EXISTS" in Postgres?

Hoping someone can steer me in the right direction.

Thanks
Kevin

--
Kevin Coyner
mailto: kevin@rustybear.com
GnuPG key: 1024D/8CE11941

Re: drop table if exists

From
Kevin Coyner
Date:

On Wed, Mar 05, 2003 at 02:09:03PM -0500, Kevin Coyner wrote......

>
> In MySQL, when I create a new table from a .sql file, I usually start
> with:
>
> DROP TABLE IF EXISTS sometable;
> CREATE TABLE sometable(
>
>
> This doesn't work in Postgres and I haven't been able to find the
> equivalent in the docs.  If I try to drop a table that doesn't exist, I
> get an error message, so having that modifier "... if exists ..." is
> nice.
>
> Is there an equivalent to "IF EXISTS" in Postgres?


No thoughts about this one?

Thanks
Kevin

--
Kevin Coyner
mailto: kevin@rustybear.com
GnuPG key: 1024D/8CE11941

Re: drop table if exists

From
Aarni Ruuhimäki
Date:
Hi !

DROP IF EXISTS is about the same as DROP ( regardless if it exists or not ) ?

pg_dump -c file does just that, i.e. use DROP, it gives an error output about
non-existent table but you can ignore that. ( 7.2.3 )

BR,


On Friday 14 March 2003 13:46, you wrote:
> On Wed, Mar 05, 2003 at 02:09:03PM -0500, Kevin Coyner wrote......
>
> > In MySQL, when I create a new table from a .sql file, I usually start
> > with:
> >
> > DROP TABLE IF EXISTS sometable;
> > CREATE TABLE sometable(
> >
> >
> > This doesn't work in Postgres and I haven't been able to find the
> > equivalent in the docs.  If I try to drop a table that doesn't exist, I
> > get an error message, so having that modifier "... if exists ..." is
> > nice.
> >
> > Is there an equivalent to "IF EXISTS" in Postgres?
>
> No thoughts about this one?
>
> Thanks
> Kevin

--
Aarni Ruuhimäki
Megative Tmi
KYMI.com

Pääsintie 26
45100 Kouvola

info@kymi.com / aarni.ruuhimaki@kymi.com

05 - 3755 035 / 050 - 4910 037

**********************
Linux RedHat / KDE
**********************

Re: drop table if exists

From
Joe Conway
Date:
Kevin Coyner wrote:
>>Is there an equivalent to "IF EXISTS" in Postgres?
>

No, there is no direct equivalent (and I have often wished there was --
but not enough to try to implement it, at least not yet).

You can fake it with a plpgsql function (very lightly tested):

CREATE OR REPLACE FUNCTION drop_table_if_exists(text, bool) RETURNS bool
AS '
DECLARE
   opt text;
   rec record;
BEGIN
   IF $2 THEN
     opt := '' CASCADE'';
   ELSE
     opt := '''';
   END IF;

   SELECT INTO rec oid FROM pg_class WHERE relname = $1::name;

   IF FOUND THEN
     EXECUTE ''DROP TABLE '' || $1 || opt;
     RETURN true;
   END IF;

   RETURN false;
END;
' LANGUAGE 'plpgsql';

regression=# SELECT drop_table_if_exists('foo', false);
  drop_table_if_exists
----------------------
  t
(1 row)

regression=# SELECT drop_table_if_exists('foo', false);
  drop_table_if_exists
----------------------
  f
(1 row)

HTH,

Joe