Re: Get a list of columns for a table

How do I retrieve a list of columns for a table from an outside application?
I don't want to retrieve any rows from the table just the column related
data like what is retrieved using '\d [table name]' in psql. Can this be
done using a simple Select statement?

Thanks for your help!

Kevin

SELECT attname, description FROM pg_class, pg_attribute, pg_description WHERE
pg_class.relname = 'codes' AND pg_class.oid = pg_attribute.attrelid AND
pg_attribute.attnum > 0 AND (objoid = pg_class.oid) AND (attnum = objsubid)
ORDER BY attname;

The above will display the following:
attname | description
----------+-------------------------
amt | Code Amount
auto | Auto Deduct Flag
code | Code
deduct | Deduct Flag
descript | Code Description
gl_acct | GL Account Number
method | Calculation Method Flag
name | Program Internal Name
percent | Percent Value
type | Code Type
(10 rows)

There are many combinations of this. It just depends on what you want.

On Wednesday 07 September 2005 06:19 pm, Kevin Crenshaw saith:
> How do I retrieve a list of columns for a table from an outside
> application? I don't want to retrieve any rows from the table just the
> column related data like what is retrieved using '\d [table name]' in psql.
> Can this be done using a simple Select statement?
>
> Thanks for your help!
>
> Kevin

Kevin,

> How do I retrieve a list of columns for a table from an outside
> application? I don't want to retrieve any rows from the table just the
> column related data like what is retrieved using '\d [table name]' in
> psql. Can this be done using a simple Select statement?

Yes. Look up INFORMATION_SCHEMA in the PostgreSQL Docs.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco