Received: from fslc.ser.usu.edu (fslc.ser.usu.edu [129.123.28.122]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8I0DUd22921 for ; Mon, 17 Sep 2001 20:13:30 -0400 (EDT) (envelope-from rmello@fslc.usu.edu) Received: from rmello by fslc.ser.usu.edu with local (Exim 3.32 #1 (Debian)) id 15j8V3-0002OY-00; Mon, 17 Sep 2001 18:12:29 -0600 Date: Mon, 17 Sep 2001 18:12:29 -0600 From: Roberto Mello To: Julester Cc: pgsql-sql@postgresql.org Subject: Re: Checking for table existence Message-ID: <20010917181229.A9170@cc.usu.edu> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.3.20i X-Archive-Number: 200109/119 X-Sequence-Number: 4790 On Fri, Sep 14, 2001 at 06:58:29PM +0000, Julester wrote: > Hi everyone. In my old SQL Server days, I used a command such as "IF > exists(select name from sys_objects where name = 'xyztable')" to check if a > table existed before creating it with a standard CREATE command. I looked > in the PostgreSQL documentation, but for the life of me, I can't find an > equivalent. I can view if the table exists by doing a select against the > meta-data tables, but what about the IF statement ? Any help would be > greatly appreciated. Thanks. You can have psql output its internal queries and that will give you some insight: roberto@brasileiro:~/documents/pictures$ psql -e foobar Welcome to psql, the PostgreSQL interactive terminal. ... foobar=# \d blah ********* QUERY ********* SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_class WHERE relname='blah' ************************* ********* QUERY ********* SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a WHERE c.relname = 'blah' AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum ************************* ********* QUERY ********* SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c WHERE c.relname = 'blah' AND c.oid = d.adrelid AND d.adnum = 1 ************************* Table "blah" Attribute | Type | Modifier -----------+--------------------------+--------------- something | timestamp with time zone | default 'now' name | character(50) -Roberto -- +------------| Roberto Mello - http://www.brasileiro.net |------------+ Computer Science, Utah State University - http://www.usu.edu USU Free Software & GNU/Linux Club - http://fslc.usu.edu Space Dynamics Lab, Developer - http://www.sdl.usu.edu OpenACS - Enterprise free web toolkit - http://openacs.org Blood is thicker than water, and much tastier.