Re: describe table query? - Mailing list pgsql-general

Yes.I want that.
regards
Haris Peco
On Wednesday 11 September 2002 12:24 am, scott.marlowe wrote:
> The second query here does that.  Or did you want it broken out in a
> seperate column (one for name, one for precision, etc...)
>
> On Tue, 10 Sep 2002, snpe wrote:
> > \d bubba
> > and select format_type ... is fine, but I want column name,column
> > type,column size,column precision with any select command (type form \d
> > exchange with column_type,column_size and column_precision)
> > Is it possible ?
> >
> > regards
> > Haris Peco
> >
> > On Tuesday 10 September 2002 06:09 pm, scott.marlowe wrote:
> > > 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@snpe.co.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@postgresql.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@postgresql.org so that your message can get through to
> > > > the mailing list cleanly
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)