Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eANGMaA67700 for ; Thu, 23 Nov 2000 11:22:36 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id eANGMjw07120; Thu, 23 Nov 2000 11:22:45 -0500 (EST) To: "Rob Burne" cc: pgsql-sql@postgresql.org Subject: Re: Help with displaying data types. In-reply-to: References: Comments: In-reply-to "Rob Burne" message dated "Thu, 16 Nov 2000 23:25:22 +0000" Date: Thu, 23 Nov 2000 11:22:44 -0500 Message-ID: <7117.974996564@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200011/200 X-Sequence-Number: 231 "Rob Burne" writes: > I am trying to perform a query that will: > 1. Return all user defined attributes from a relation. > 2. Also return the datatypes of each relation. > So far I can only achieve part 1 with the following: > select attname from pg_class,pg_attribute > where relname=relation_name and > attrelid = pg_class.oid and > atttypid != 26 and > atttypid != 27 and > atttypid != 28 and > atttypid != 29; That's a bad way to do it, because your query will drop user-defined columns of type OID (among others). I'd suggest testing for attnum > 0 to get rid of system columns. > But what do I need to add to return the datatype of each attribute. Any > suggestions please? Join against pg_type. For example, select attname, typname from pg_class c, pg_attribute a, pg_type t where relname = relation_name and attrelid = c.oid and atttypid = t.oid and attnum > 0 order by attnum; psql does a lot of these sorts of queries for its \d features. Try starting psql with -E so you can see what queries it issues when you do a \d ... regards, tom lane