Thread: Get a list of columns for a table

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

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