pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Is there anything like DESCRIBE?
6+ messages / 5 participants
[nested] [flat]

* Is there anything like DESCRIBE?
@ 2001-01-24 19:00 Mike D'Agosta <mdagosta@earthtribe.net>
  2001-01-25 20:56 ` Re: Is there anything like DESCRIBE? Mike D'Agosta <mdagosta@earthtribe.net>
  2001-01-25 21:02 ` Re: Is there anything like DESCRIBE? clayton cottingham <drfrog@smartt.com>
  2001-01-25 22:51 ` Re: Is there anything like DESCRIBE? Oliver Elphick <olly@lfix.co.uk>
  2001-01-26 02:34 ` Re: Is there anything like DESCRIBE? Ron Peterson <ron.peterson@yellowbank.com>
  0 siblings, 4 replies; 6+ messages in thread

From: Mike D'Agosta @ 2001-01-24 19:00 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Is there anything like DESCRIBE?
  2001-01-24 19:00 Is there anything like DESCRIBE? Mike D'Agosta <mdagosta@earthtribe.net>
@ 2001-01-25 20:56 ` Mike D'Agosta <mdagosta@earthtribe.net>
  3 siblings, 0 replies; 6+ messages in thread

From: Mike D'Agosta @ 2001-01-25 20:56 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Is there anything like DESCRIBE?
  2001-01-24 19:00 Is there anything like DESCRIBE? Mike D'Agosta <mdagosta@earthtribe.net>
@ 2001-01-25 21:02 ` clayton cottingham <drfrog@smartt.com>
  3 siblings, 0 replies; 6+ messages in thread

From: clayton cottingham @ 2001-01-25 21:02 UTC (permalink / raw)
  To: Mike D'Agosta <mdagosta@earthtribe.net>; +Cc: pgsql-sql

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 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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Is there anything like DESCRIBE?
  2001-01-24 19:00 Is there anything like DESCRIBE? Mike D'Agosta <mdagosta@earthtribe.net>
@ 2001-01-25 22:51 ` Oliver Elphick <olly@lfix.co.uk>
  3 siblings, 0 replies; 6+ messages in thread

From: Oliver Elphick @ 2001-01-25 22:51 UTC (permalink / raw)
  To: Mike D'Agosta <mdagosta@earthtribe.net>; +Cc: pgsql-sql

"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?


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 





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Is there anything like DESCRIBE?
  2001-01-24 19:00 Is there anything like DESCRIBE? Mike D'Agosta <mdagosta@earthtribe.net>
@ 2001-01-26 02:34 ` Ron Peterson <ron.peterson@yellowbank.com>
  3 siblings, 0 replies; 6+ messages in thread

From: Ron Peterson @ 2001-01-26 02:34 UTC (permalink / raw)
  To: Mike D'Agosta <mdagosta@earthtribe.net>; +Cc: pgsql-sql

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-



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* RE: Is there anything like DESCRIBE?
@ 2001-01-25 20:57 Michael Davis <mdavis@sevainc.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Michael Davis @ 2001-01-25 20:57 UTC (permalink / raw)
  To: 'Mike D'Agosta' <mdagosta@earthtribe.net>; pgsql-sql

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 <tablename> in psql). Postgres doesn't
support DESCRIBE... is there any other way to do this?

Thanks!
Mike






^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-01-26 02:34 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-24 19:00 Is there anything like DESCRIBE? Mike D'Agosta <mdagosta@earthtribe.net>
2001-01-25 20:56 ` Mike D'Agosta <mdagosta@earthtribe.net>
2001-01-25 21:02 ` clayton cottingham <drfrog@smartt.com>
2001-01-25 22:51 ` Oliver Elphick <olly@lfix.co.uk>
2001-01-26 02:34 ` Ron Peterson <ron.peterson@yellowbank.com>
2001-01-25 20:57 RE: Is there anything like DESCRIBE? Michael Davis <mdavis@sevainc.com>

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