Thread: DROP TABLE IF EXISTS
I am wondering how to do this simple mysql task in postgres. Any hints? Thanks, Sean
* Sean Davis <sdavis2@mail.nih.gov> [2004-10-21 12:26:47 -0400]: > I am wondering how to do this simple mysql task in postgres. Any > hints? This reply from Ron Johnson seems to suffice: http://archives.postgresql.org/pgsql-interfaces/2002-05/msg00102.php -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564
I'm not sure why this even matters. You do DROP TABLE on a table that doesn't exist, all that will happen is that you will get an error back but your program will continue on it's merry way anyways. The only thing IF EXISTS would give you is the suppression of the error message. Steven Klassen wrote: > * Sean Davis <sdavis2@mail.nih.gov> [2004-10-21 12:26:47 -0400]: > > >>I am wondering how to do this simple mysql task in postgres. Any >>hints? > > > This reply from Ron Johnson seems to suffice: > > http://archives.postgresql.org/pgsql-interfaces/2002-05/msg00102.php >
Point taken. However, in a perl/DBI setting, the program does die unless I explicitly trap the error, etc. So, it is a convenience, true enough, but not an issue that I had to deal with in MySQL, so just thought I would ask. Sean -----Original Message----- From: William Yu To: pgsql-novice@postgresql.org Sent: 10/25/2004 2:05 PM Subject: Re: [NOVICE] DROP TABLE IF EXISTS I'm not sure why this even matters. You do DROP TABLE on a table that doesn't exist, all that will happen is that you will get an error back but your program will continue on it's merry way anyways. The only thing IF EXISTS would give you is the suppression of the error message. Steven Klassen wrote: > * Sean Davis <sdavis2@mail.nih.gov> [2004-10-21 12:26:47 -0400]: > > >>I am wondering how to do this simple mysql task in postgres. Any >>hints? > > > This reply from Ron Johnson seems to suffice: > > http://archives.postgresql.org/pgsql-interfaces/2002-05/msg00102.php > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
That's a new one to me. I use perl/DBI also and my scripts just ignore errors w/o any error trapping. Maybe my defaults are set to ignore errors. I only trap errors when I there's a possibility of catastrophic failure. (Example, DB is down -- SELECT * FROM zzz returns an error so the row count is 0 -- page looks normal except there's no content versus some generic apache error message.) Davis, Sean (NIH/NHGRI) wrote: > Point taken. However, in a perl/DBI setting, the program does die unless I > explicitly trap the error, etc. So, it is a convenience, true enough, but > not an issue that I had to deal with in MySQL, so just thought I would ask.
--- William Yu <wyu@talisys.com> escribió: > I'm not sure why this even matters. You do DROP > TABLE on a table that > doesn't exist, all that will happen is that you will > get an error back > but your program will continue on it's merry way > anyways. The only thing > IF EXISTS would give you is the suppression of the > error message. > > > Steven Klassen wrote: > > > * Sean Davis <sdavis2@mail.nih.gov> [2004-10-21 > 12:26:47 -0400]: > > > > > >>I am wondering how to do this simple mysql task in > postgres. Any > >>hints? > > > > > > This reply from Ron Johnson seems to suffice: > > > > > http://archives.postgresql.org/pgsql-interfaces/2002-05/msg00102.php what about the Ron Johnson solution? if exists (select 1 from pg_tables where tablename = "thetable") > drop table thetable regards, Jaime Casanova _________________________________________________________ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com
Jaime, Thanks for the reply. On Oct 26, 2004, at 4:51 PM, Jaime Casanova wrote: > http://archives.postgresql.org/pgsql-interfaces/2002-05/msg00102.php > > > what about the Ron Johnson solution? > if exists (select 1 from pg_tables where tablename = > "thetable") >> drop table thetable >> Actually, Ron gave some other possibilities, but the query above does NOT work (and was the original source of the question). Just for information, here is a function that I had come up with that works. It returns 1 or 0 just as a sanity check. create or replace function drop_if_exists (text) returns INTEGER AS ' DECLARE tbl_name ALIAS FOR $1; BEGIN IF (select count(*) from pg_tables where tablename=$1) THEN EXECUTE ''DROP TABLE '' || $1; RETURN 1; END IF; RETURN 0; END; ' language 'plpgsql'; Sean