Re: describe table query?

There are two ways to do this. One is the postgresql specific way, which
is to crank up psql with the -E switch, then issue a \d for a table, and
copy out the sql query that goes by. On my 7.2.1 box, that gives me a
set of queries like so for a table named 'bubba':
smarlowe=# \d bubba

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='bubba'
**************************

This NEXT one describes the table for us:

********* QUERY **********
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = 'bubba'
AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
**************************

This one tells us what indexes it has:

********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
AND NOT i.indisunique ORDER BY c2.relname
**************************

I'm not sure what the next two do, I think they have to do with foreign
keys.
********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
AND i.indisprimary AND i.indisunique ORDER BY c2.relname
**************************

********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
AND NOT i.indisprimary AND i.indisunique ORDER BY c2.relname
**************************

This one gives us our constraints:
********* QUERY **********
SELECT rcsrc, rcname
FROM pg_relcheck r, pg_class c
WHERE c.relname='bubba' AND c.oid = r.rcrelid
**************************

The other way to do it is to issue a single query of the form "Select *
from table limit 1" and use pg_num_fields, pg_field_name and
pg_field_type commands to walk the returned fields to find their name and
type. The advantage of this method is that it is somewhat more
transportable to other dbmses.

On Mon, 9 Sep 2002, Dan Ostrowski wrote:

> I also would love to know how you do this, because I am REALLY missing the "DESCRIBE <table>" calls...
>
> I work with mostly PHP4...
>
> please help!
>
> regards,
> dan
>
> On Tue, 10 Sep 2002 03:07:46 +0200
> snpe <snpe(at)snpe(dot)co(dot)yu> wrote:
>
> > If You use java then that is DatabaseMetaData.getColumns etc
> >
> > regards
> > Haris Peco
> > On Monday 09 September 2002 11:30 pm, Andrew Bulmer wrote:
> > > I'm trying to write an Access clone in java that will
> > > use PostGres as a backend. Problem is, I need to be
> > > able to list all the fields (and data types) in a
> > > table. I know about "\d" but that only seems to work
> > > on the command line client (doesn't work if I pass it
> > > in as a query). I know in mysql DESCRIBE <table> will
> > > do it... is there an equivalent in postgres? I tried
> > > google but all I could find were references to the \d command.
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Yahoo! Finance - Get real-time stock quotes
> > > http://finance.yahoo.com
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>