Thread: RE: Test for existence of Table
Yeah,
DROP TABLE employees
CREATE TABLE employees
;-)
Craig
-----Original Message-----
From: Soma Interesting [mailto:dfunct@telus.net]
Sent: Thursday, January 04, 2001 1:40 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Test for existence of Table
Can I test for an existing table before issuing the "CREATE TABLE" command?
IF EXISTS employees{
DROP TABLE employees
}
CREATE TABLE employees
Can you provide an example of this. I couldn't find this in the manual or
Bruce's book - did I not look hard enough ? :)
- - - - - - -
- - - -
WARNING: Some experts believe that use of any keyboard may cause
serious injury. Consult Users Guide.
dfunct@telus.net
> DROP TABLE employees
Error: ERROR: Relation 'employees' does not exist
And execution halts.... which is I believe why he wanted to check for the
existence before trying to DROP. I'd love to know if this exists as well...
would come in very handy during development time.
Greg
> Yeah,
>
> DROP TABLE employees
> CREATE TABLE employees
>
> ;-)
>
> Craig
>
> -----Original Message-----
> From: Soma Interesting [mailto:dfunct@telus.net]
> Sent: Thursday, January 04, 2001 1:40 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Test for existence of Table
>
>
> Can I test for an existing table before issuing the "CREATE TABLE"
command?
>
> IF EXISTS employees{
> DROP TABLE employees
> }
> CREATE TABLE employees
>
> Can you provide an example of this. I couldn't find this in the manual or
> Bruce's book - did I not look hard enough ? :)
>
>
> - - - - - - -
> - - - -
> WARNING: Some experts believe that use of any keyboard may cause
> serious injury. Consult Users Guide.
>
> dfunct@telus.net
>
On Thu, 04 Jan 2001 22:11:31 Gregory Wood wrote: >> DROP TABLE employees >Error: ERROR: Relation 'employees' does not exist > >And execution halts.... which is I believe why he wanted to check for the >existence before trying to DROP. I'd love to know if this exists as well... >would come in very handy during development time. > >Greg I just tested this with "psql < script.sql" and execution did not stop. There may be contexts where this error causes execution to stop but it works at the command line with psql. That implies that it works within psql using "\i script.sql", which I have also found to be true. Tony -- Anthony E. Greene <agreene@pobox.com> <http://www.pobox.com/~agreene/> PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26 C484 A42A 60DD 6C94 239D Chat: AOL/Yahoo: TonyG05 ICQ: 91183266 Linux. The choice of a GNU Generation. <http://www.linux.org/>
>-----Original Message----- >From: Gregory Wood [mailto:gregw@com-stock.com] >Sent: Thursday, January 04, 2001 9:12 PM >To: PostgreSQL-General >Subject: [GENERAL] Re: Test for existence of Table > > >> DROP TABLE employees >Error: ERROR: Relation 'employees' does not exist > >And execution halts.... which is I believe why he wanted to check for the >existence before trying to DROP. I'd love to know if this exists as well... >would come in very handy during development time. > Execution does not halt, it continues and creates the table. I rely on this functionality, so I know it works. Good luck! > >Greg Cheers, Craig
> >> DROP TABLE employees > >Error: ERROR: Relation 'employees' does not exist > > > >And execution halts.... which is I believe why he wanted to check for the > >existence before trying to DROP. I'd love to know if this exists as well... > >would come in very handy during development time. > > Execution does not halt, it continues and creates the table. I rely on this > functionality, so I know it works. Good luck! Well, that is a quote from the database tool I use, and execution does indeed halt. I can only assume you are using psql, which someone confirmed that that does work. Also, if you wrap your queries in an explicit transaction (I don't like to leave anything to Auto-Commit), it will also fail: NOTICE: current transaction is aborted, queries ignored until end of transaction block What would be nice is if there were a way to only DROP a table if it exists. But I would consider this to be rather low priority. Greg
Gregory Wood wrote:
>
> What would be nice is if there were a way to only DROP a table if it exists.
> But I would consider this to be rather low priority.
This might help...
CREATE FUNCTION table_exists(TEXT) RETURNS BOOLEAN AS
'DECLARE
tablename ALIAS FOR $1;
temp RECORD;
BEGIN
SELECT INTO temp *
FROM pg_class c
WHERE c.relname = tablename
AND c.relkind = ''r'';
if found then
return ''t''::BOOLEAN;
else
return ''f''::BOOLEAN;
end if;
END;'
LANGUAGE 'plpgsql';
-- test table
CREATE TABLE realtable (id INTEGER);
-- test example
SELECT table_exists('realtable'::TEXT);
SELECT table_exists('faketable'::TEXT);
-- clean up
DROP TABLE realtable;
DROP FUNCTION table_exists(TEXT);
It'd be even nicer if you could drop the table from within the PL/pgSQL
function, but I found that does not work in 7.0.0.