X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6B128D772A for ; Sat, 5 Nov 2005 14:13:54 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23218-01 for ; Sat, 5 Nov 2005 18:13:50 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3C07FD6876 for ; Sat, 5 Nov 2005 14:13:49 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 9B5E631059; Sat, 5 Nov 2005 19:13:49 +0100 (MET) From: Christopher Browne X-Newsgroups: pgsql.sql Subject: Re: Extract table columns in tabular form Date: Sat, 05 Nov 2005 13:14:12 -0500 Organization: cbbrowne Computing Inc Lines: 24 Message-ID: References: <1131209276.436ce23cba2d2@webmail.adontendev.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Jumbo Shrimp, berkeley-unix) Cancel-Lock: sha1:nb40CtAxipVKDbMcPmIixcdp5dc= To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.501 required=5 tests=[AWL=-0.312, INFO_TLD=0.813] X-Spam-Score: 0.501 X-Spam-Level: X-Archive-Number: 200511/62 X-Sequence-Number: 23313 > How do you extract postgresql column names, types and comments in tabular form, > using an SQL command?. I know they are stored in one of the system tables, but > I don't know which. - Table names are in pg_class - Column names are in pg_attribute, associated to pg_class via "where pg_attribute.attrelid = pg_class.oid" - Comments are in pg_description... There seem to be links to both pg_class and pg_attribute - Type info is in pg_type, associated to pg_attribute via "atttypid" There are attributes that you normally don't see, and "dropped" attributes still exist in pg_attribute, so things are a tad more complex than what I have said, but the above 4 tables should provide you all you need... -- select 'cbbrowne' || '@' || 'acm.org'; http://linuxdatabases.info/info/slony.html Never criticize anybody until you have walked a mile in their shoes, because by that time you will be a mile away and have their shoes. -- email sig, Brian Servis