From: Colin Wetherbee <cww@denterprises.org>
To: danap@dandymadeproductions.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Describe Table
Date: Mon, 17 Dec 2007 12:34:27 -0500
Message-ID: <4766B323.6030102@denterprises.org> (raw)
In-Reply-To: <20071217165211.E18222E3202@postgresql.org>
References: <20071217165211.E18222E3202@postgresql.org>
danap@dandymadeproductions.com wrote:
> I've reviewed much of the documentation and the forums,
> but unable to seem to find a easy way to obtain the same
> thing as the 'psql \d table' through sql. I know I can create
> through collecting info on table, but seems there must be an
> easier way. I desire to create a standard type SQL dump
> syntax.
Briefly, you use the special pg_ tables [0].
The following query is probably not the most efficient way of doing it,
but it shows the column names for the "wines" table. The first seven
listed are system columns (tableoid - ctid), and the rest are data
columns (name - score).
You can look at the descriptions for each of the pg_ tables to refine
your query a bit, exclude system columns, figure out data types, and so
forth.
cww=# SELECT pg_class.relname, attname FROM pg_attribute, pg_class WHERE
attrelid = pg_class.reltype::integer - 1 AND pg_class.relname = 'wines';
relname | attname
---------+-----------------
wines | tableoid
wines | cmax
wines | xmax
wines | cmin
wines | xmin
wines | oid
wines | ctid
wines | name
wines | vintage
wines | origin
wines | specific_origin
wines | color
wines | type
wines | description
wines | vintner
wines | entry_date
wines | score
(17 rows)
This query works on 8.1.9.
Colin
[0]
http://www.postgresql.org/files/documentation/books/aw_pgsql/node183.html
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cww@denterprises.org, danap@dandymadeproductions.com
Subject: Re: Describe Table
In-Reply-To: <4766B323.6030102@denterprises.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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