Re: Clarification question

"Tim Barnard" <tbarnard(at)povn(dot)com> writes:
> Is the following select sufficient and correct for extracting the column
> names of a table, excluding all system columns?

> select attname from pg_attribute
> where attrelid=
> (select relfilenode from pg_class
> where relname like <insert table name here>)
> and attnum > 0

relfilenode is definitely the wrong thing; use pg_class.oid instead.
(Presently they are usually if not always equal, but the reason we
put in a relfilenode column is that we intend to make them different
someday soon.) Also I'd use a plain "=" not "like", if I know I am
looking for just one table. So

select attname from pg_attribute
where attrelid=
(select oid from pg_class
where relname = '<insert table name here>')
and attnum > 0

> I want to be certain that no system columns are returned, only columns I've
> created.

attnum > 0 is the right way to handle that.

BTW, it is likely that in 7.3 relname will not be a unique key for
pg_class anymore; you'll be needing to check which schema the table
is in, too. There's not much you can do about this now. Just be
aware that the system catalogs do tend to change over time.

regards, tom lane