From mdagosta@earthtribe.net Thu Jan 25 19:12:06 2001 Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0PJC6q11182 for ; Thu, 25 Jan 2001 14:12:06 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0OJTZq59337 for ; Wed, 24 Jan 2001 14:29:35 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f0OJ6Sa85206 for pgsql-sql@postgresql.org; Wed, 24 Jan 2001 14:06:28 -0500 (EST) (envelope-from news) From: "Mike D'Agosta" X-Newsgroups: comp.databases.postgresql.sql Subject: Is there anything like DESCRIBE? Date: Wed, 24 Jan 2001 11:00:33 -0800 Organization: earthtribe.net Lines: 11 Message-ID: <94n93j$2j6j$1@news.tht.net> Reply-To: "Mike D'Agosta" X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 To: pgsql-sql@postgresql.org X-Archive-Number: 200101/363 X-Sequence-Number: 1036 Hi, I have a number of empty tables and I want to get the column names and data types with an SQL statement. I want to do this procedurally, not interactively (so I can't use \d in psql). Postgres doesn't support DESCRIBE... is there any other way to do this? Thanks! Mike From mdagosta@earthtribe.net Sat Jan 27 00:54:52 2001 Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0R0sqq68899 for ; Fri, 26 Jan 2001 19:54:52 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0PLTdq25325 for ; Thu, 25 Jan 2001 16:29:39 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f0PL2C605843 for pgsql-sql@postgresql.org; Thu, 25 Jan 2001 16:02:12 -0500 (EST) (envelope-from news) From: "Mike D'Agosta" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Is there anything like DESCRIBE? Date: Thu, 25 Jan 2001 12:56:17 -0800 Organization: earthtribe.net Lines: 25 Message-ID: <94q48i$5me$1@news.tht.net> References: <94n93j$2j6j$1@news.tht.net> Reply-To: "Mike D'Agosta" X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 To: pgsql-sql@postgresql.org X-Archive-Number: 200101/404 X-Sequence-Number: 1080 I got a response in email, so I assume the author wants to remain anonymous. He wrote: >There's an option to psql (I think -E or -e) that will make it echo all >the SQL queries it does when you do something like "\d table" So running a "psql -E " and then doing a "\d " will give you information similar to what you would normally get from a DESCRIBE. Mike ---------------------------------------------------------------------------- -------------------------- "Mike D'Agosta" wrote in message news:94n93j$2j6j$1@news.tht.net... > Hi, > > I have a number of empty tables and I want to get the column names and > data types with an SQL statement. I want to do this procedurally, not > interactively (so I can't use \d in psql). Postgres doesn't > support DESCRIBE... is there any other way to do this? > > Thanks! > Mike From mdavis@sevainc.com Thu Jan 25 20:57:19 2001 Received: from mail01.phoenixdsl.com (mail.phoenixdsl.com [216.178.151.24]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0PKvIq22686 for ; Thu, 25 Jan 2001 15:57:19 -0500 (EST) (envelope-from mdavis@sevainc.com) Received: (qmail 57572 invoked by uid 89); 25 Jan 2001 20:57:14 -0000 Received: from unknown (HELO SALLY) (64.32.157.45) by 0 with SMTP; 25 Jan 2001 20:57:14 -0000 Received: by localhost with Microsoft MAPI; Thu, 25 Jan 2001 13:57:13 -0700 Message-ID: <01C086D6.B80EB9B0.mdavis@sevainc.com> From: Michael Davis Reply-To: "mdavis@sevainc.com" To: "'Mike D'Agosta'" , "pgsql-sql@postgresql.org" Subject: RE: Is there anything like DESCRIBE? Date: Thu, 25 Jan 2001 13:57:08 -0700 Organization: Seva Inc. X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/368 X-Sequence-Number: 1041 This works for me: SELECT DISTINCT c.relname as table_name, a.attname as column_name, t.typname, pa.adsrc as default FROM (pg_attribute a join pg_class c on a.attrelid = c.oid join pg_type t on a.atttypid = t.oid) left join pg_attrdef pa on c.oid = pa.adrelid AND a.attnum = pa.adnum where exists (select * from pg_tables where tablename = c.relname and substr(tablename,1,2) <> 'pg') order by c.relname, a.attname; -----Original Message----- From: Mike D'Agosta [SMTP:mdagosta@earthtribe.net] Sent: Wednesday, January 24, 2001 12:01 PM To: pgsql-sql@postgresql.org Subject: Is there anything like DESCRIBE? Hi, I have a number of empty tables and I want to get the column names and data types with an SQL statement. I want to do this procedurally, not interactively (so I can't use \d in psql). Postgres doesn't support DESCRIBE... is there any other way to do this? Thanks! Mike From drfrog@smartt.com Thu Jan 25 21:00:44 2001 Received: from clayton-cottingham.imc ([216.18.11.61]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0PL0iq23418 for ; Thu, 25 Jan 2001 16:00:44 -0500 (EST) (envelope-from drfrog@smartt.com) Received: from smartt.com (IDENT:drfrog@localhost.localdomain [127.0.0.1]) by clayton-cottingham.imc (8.11.0/8.11.0) with ESMTP id f0PL25p11039; Thu, 25 Jan 2001 13:02:05 -0800 Message-ID: <3A70944D.4541D0F3@smartt.com> Date: Thu, 25 Jan 2001 13:02:05 -0800 From: clayton cottingham X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.17-21mdk i686) X-Accept-Language: en MIME-Version: 1.0 To: "Mike D'Agosta" CC: pgsql-sql@postgresql.org Subject: Re: Is there anything like DESCRIBE? References: <94n93j$2j6j$1@news.tht.net> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/369 X-Sequence-Number: 1042 Mike D'Agosta wrote: > > Hi, > > I have a number of empty tables and I want to get the column names and > data types with an SQL statement. I want to do this procedurally, not > interactively (so I can't use \d in psql). Postgres doesn't > support DESCRIBE... is there any other way to do this? > > Thanks! > Mike like this drfrog=# \d messages Table "messages" Attribute | Type | Modifier -------------------+---------+---------- id | integer | not null msgid | text | boxid | integer | accountid | integer | date | text | sentto | text | sentfrom | text | subject | text | contenttype | text | contentxferencode | text | mimeversion | text | precedence | text | approvedby | text | inreplyto | text | replyto | text | listsub | text | listunsub | text | status | text | xorigip | text | cc | text | bcc | text | sender | text | returnpath | text | priority | text | xmailer | text | xuidl | text | xsender | text | localdate | text | newmsg | text | replyf | integer | friendly | text | rreceiptto | text | score | integer | ref | text | serverstat | integer | Index: messages_pkey From olly@lfix.co.uk Thu Jan 25 22:51:36 2001 Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0PMpaq30692 for ; Thu, 25 Jan 2001 17:51:36 -0500 (EST) (envelope-from olly@lfix.co.uk) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by anchor-post-31.mail.demon.net with esmtp (Exim 2.12 #1) id 14LvEr-000GlW-0V; Thu, 25 Jan 2001 22:51:34 +0000 Received: from lfix.co.uk (olly@localhost [127.0.0.1]) by linda.lfix.co.uk (8.11.2/8.11.2/Debian 8.11.2-1) with ESMTP id f0PMpUF28860; Thu, 25 Jan 2001 22:51:30 GMT Message-Id: <200101252251.f0PMpUF28860@linda.lfix.co.uk> X-Mailer: exmh version 2.2 06/23/2000 (debian 2.2-1) with nmh-1.0.4+dev X-URL: http://www.lfix.co.uk/oliver X-face: "xUFVDj+ZJtL_IbURmI}!~xAyPC"Mrk=MkAm&tPQnNq(FWxv49R}\>0oI8VM?O2VY+N7@F- KMLl*!h}B)u@TW|B}6 cc: pgsql-sql@postgresql.org Subject: Re: Is there anything like DESCRIBE? In-reply-to: Message from "Mike D'Agosta" of Wed, 24 Jan 2001 11:00:33 PST. <94n93j$2j6j$1@news.tht.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Thu, 25 Jan 2001 22:51:30 +0000 From: "Oliver Elphick" X-Archive-Number: 200101/370 X-Sequence-Number: 1043 "Mike D'Agosta" wrote: >Hi, > > I have a number of empty tables and I want to get the column names and >data types with an SQL statement. I want to do this procedurally, not >interactively (so I can't use \d in psql). Postgres doesn't >support DESCRIBE... is there any other way to do this? If you run psql with the -E option, it will show you the query it uses when you type `\d+`. Use that query in your procedural code. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "My little children, let us not love in word, neither in tongue; but in deed and in truth." I John 3:18 From ron.peterson@yellowbank.com Fri Jan 26 02:34:53 2001 Received: from h00a024ee7419.ne.mediaone.net (h00a024ee7419.ne.mediaone.net [24.91.215.71]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0Q2Yrq44757 for ; Thu, 25 Jan 2001 21:34:53 -0500 (EST) (envelope-from ron.peterson@yellowbank.com) Received: from yellowbank.com (IDENT:yrp001@h00a024ee7419.ne.mediaone.net [24.91.215.71]) by h00a024ee7419.ne.mediaone.net (8.11.1/8.11.1) with ESMTP id f0Q2Yes04691; Thu, 25 Jan 2001 21:34:40 -0500 Message-ID: <3A70E240.64FC7006@yellowbank.com> Date: Thu, 25 Jan 2001 21:34:40 -0500 From: Ron Peterson Organization: Yellowbank X-Mailer: Mozilla 4.75 [en] (X11; U; Linux 2.4.0-test10 i586) X-Accept-Language: en MIME-Version: 1.0 To: "Mike D'Agosta" CC: pgsql-sql@postgresql.org Subject: Re: Is there anything like DESCRIBE? References: <94n93j$2j6j$1@news.tht.net> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/373 X-Sequence-Number: 1046 Mike D'Agosta wrote: > > Hi, > > I have a number of empty tables and I want to get the column names and > data types with an SQL statement. I want to do this procedurally, not > interactively (so I can't use \d in psql). Postgres doesn't > support DESCRIBE... is there any other way to do this? src/tutorial/syscat.source has some examples you might like to study. -Ron-