Thread: 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 <tablename> in psql). Postgres doesn't support DESCRIBE... is there any other way to do this? Thanks! Mike
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
paon 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 <tablename> in psql). Postgres doesn't
support DESCRIBE... is there any other way to do this?
Thanks!
Mike
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 <tablename> 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 nullmsgid | 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
"Mike D'Agosta" wrote: >Hi, > > I have a number of empty tables and I want to get the column names and >data types withan SQL statement. I want to do this procedurally, not >interactively (so I can't use \d <tablename> in psql). Postgresdoesn'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 deedand in truth." I John 3:18
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 <tablename> 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-
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 <dbname>" and then doing a "\d <table>" will give you information similar to what you would normally get from a DESCRIBE. Mike ---------------------------------------------------------------------------- -------------------------- "Mike D'Agosta" <mdagosta@earthtribe.net> 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 <tablename> in psql). Postgres doesn't > support DESCRIBE... is there any other way to do this? > > Thanks! > Mike