Thread: Select all table column names for a specified tablename (per the system catalogs)

Hi List;

I want to pull the list of column names from the system catalogs for a
specified table. I only want column names, I want to exclude any index
names, or other non-column name rows.  I got this far (see below)
however this query produces additional rows with attname's like
tableoid, cmax, xmax ctid, etc.

select attname from pg_attribute where attrelid = (select oid from
pg_class where relname = 'my_tablename');

Any thoughts ?

Thanks in advance...

On Mon, 22 Sep 2008 16:06:06 -0600
kevin kempter <kevin@kevinkempterllc.com> wrote:

> Hi List;
>
> I want to pull the list of column names from the system catalogs for
> a specified table. I only want column names, I want to exclude any
> index names, or other non-column name rows.  I got this far (see
> below) however this query produces additional rows with attname's
> like tableoid, cmax, xmax ctid, etc.
>
> select attname from pg_attribute where attrelid = (select oid from
> pg_class where relname = 'my_tablename');


SELECT column_name FROM information_schema.columns WHERE table_name =
'table';

Joshua D. Drake

>
> Any thoughts ?
>
> Thanks in advance...
>
>
>


--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua Drake <jd@commandprompt.com> writes:
> kevin kempter <kevin@kevinkempterllc.com> wrote:
>> ... I got this far (see
>> below) however this query produces additional rows with attname's
>> like tableoid, cmax, xmax ctid, etc.
>>
>> select attname from pg_attribute where attrelid = (select oid from
>> pg_class where relname = 'my_tablename');

> SELECT column_name FROM information_schema.columns WHERE table_name =
> 'table';

If you don't want to use information_schema (which you might well not
want to, because it's a bit slow), the bits you're missing are that you
want to exclude columns with attnum <= 0 (system columns) as well as
those with attisdropped (dropped columns).  I don't care for the
subselect part of this either, mainly because it is not schema-aware.
Consider

select attname from pg_attribute
where attrelid = 'my_tablename'::regclass
      and attnum > 0 and not attisdropped;

which generalizes to

select attname from pg_attribute
where attrelid = 'my_schema.my_tablename'::regclass
      and attnum > 0 and not attisdropped;

whereas your original will not handle that easily.

            regards, tom lane