Date: Wed, 06 Feb 2008 12:33:25 -0800
To: pgsql-sql@postgresql.org
From: Steve Midgley <public@misuse.org>
Subject: Columns view? (Finding column names for a table)
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
Message-Id: <20080206203334.8B2B52E01F8@postgresql.org>

Hi,

I see this documentation item but can't figure out how to use it:

http://www.postgresql.org/docs/8.2/interactive/infoschema-columns.html

>The view columns contains information about all table columns (or view 
>columns) in the database.

However, if I execute "select columns;" I get a not found error. I'm 
sure there's some simple explanation - I'm interested specifically in 
listing the column names of a specific table (in Pg 8.2). The manual 
SQL I've developed is (where [table_name] is the table I want columns 
for):

          select pg_attribute.attname, * from pg_attribute
           join pg_class on pg_class.oid = pg_attribute.attrelid
         where
           pg_class.relname = '[table_name]'
           and
           attnum > 0
           and atttypid > 0

Not pretty but seems to work. Of course if there were a view that 
encapsulated this and future-proofed it, that'd be much nicer.

Any assistance is appreciated! Thanks,

Steve