Columns view? (Finding column names for a table)

From: Steve Midgley <public(at)misuse(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: Columns view? (Finding column names for a table)
Date: 2008-02-06 20:33:25
Message-ID: 20080206203334.8B2B52E01F8@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Thomas Kellerer 2008-02-06 20:43:44 Re: Columns view? (Finding column names for a table)
Previous Message TJ O'Donnell 2008-02-06 19:40:30 Create Table xtest (like xtype)