pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Help with displaying data types. 2+ messages / 2 participants [nested] [flat]
* Help with displaying data types. @ 2000-11-16 23:25 Rob Burne <robburne@hotmail.com> 2000-11-23 16:22 ` Re: Help with displaying data types. Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Rob Burne @ 2000-11-16 23:25 UTC (permalink / raw) To: pgsql-sql 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; But what do I need to add to return the datatype of each attribute. Any suggestions please? Regards, Rob. _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help with displaying data types. 2000-11-16 23:25 Help with displaying data types. Rob Burne <robburne@hotmail.com> @ 2000-11-23 16:22 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2000-11-23 16:22 UTC (permalink / raw) To: Rob Burne <robburne@hotmail.com>; +Cc: pgsql-sql "Rob Burne" <robburne@hotmail.com> 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-11-23 16:22 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2000-11-16 23:25 Help with displaying data types. Rob Burne <robburne@hotmail.com> 2000-11-23 16:22 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox