Thread: Clarification question
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
I want to be certain that no system columns are returned, only columns I've
created.
Am I overlooking anything?
Thanks!
Tim Barnard
S.E. Mgr
Hartford Communications Corporation
Thanks Tom. Tim ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Tim Barnard" <tbarnard@povn.com> Cc: <pgsql-general@postgresql.org> Sent: Saturday, January 19, 2002 3:22 PM Subject: Re: [GENERAL] Clarification question > "Tim Barnard" <tbarnard@povn.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 >
"Tim Barnard" <tbarnard@povn.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
Thanks for the suggestion. Tim Barnard S.E. Mgr Hartford Communications Corporation ----- Original Message ----- From: "Tommi Mäkitalo" <t.maekitalo@epgmbh.de> To: "Tim Barnard" <tbarnard@povn.com> Sent: Sunday, January 20, 2002 10:53 AM Subject: Re: [GENERAL] Clarification question > Hi, > > You could use a join too. It will be simpler to optimize it: > > select attname > from pg_attribute join pg_class on attrelid = oid > where relname = '<insert table name here>' > and attnum > 0 > > Tommi > > ... > > >>>select attname from pg_attribute > >>> where attrelid= > >>> (select relfilenode from pg_class > >>> where relname like <insert table name here>) > >>> and attnum > 0 > >>> > ... > > >> > >>select attname from pg_attribute > >> where attrelid= > >> (select oid from pg_class > >> where relname = '<insert table name here>') > >> and attnum > 0 > >> > > >
Just a note... In order to get your suggestion to work I had to alter it slightly: select attname from pf_attribute join pg_class p1 on attrelid=p1.oid where relname='<insert table name here>' and attnum > 0 Without the alias I get a "Column reference oid is ambiguous" error. Tim Barnard S.E. Mgr Hartford Communications Corporation ----- Original Message ----- From: "Tommi Mäkitalo" <t.maekitalo@epgmbh.de> To: "Tim Barnard" <tbarnard@povn.com> Sent: Sunday, January 20, 2002 10:53 AM Subject: Re: [GENERAL] Clarification question > Hi, > > You could use a join too. It will be simpler to optimize it: > > select attname > from pg_attribute join pg_class on attrelid = oid > where relname = '<insert table name here>' > and attnum > 0 > > Tommi > > ... > > >>>select attname from pg_attribute > >>> where attrelid= > >>> (select relfilenode from pg_class > >>> where relname like <insert table name here>) > >>> and attnum > 0 > >>> > ... > > >> > >>select attname from pg_attribute > >> where attrelid= > >> (select oid from pg_class > >> where relname = '<insert table name here>') > >> and attnum > 0 > >> > > >
Make that... select attname from pg_attribute join pg_class p1 on attrelid=p1.oid where relname='<insert table name here>' and attnum > 0 Sorry for the fat-fingering :-) Tim Barnard S.E. Mgr Hartford Communications Corporation ----- Original Message ----- From: "Tommi Mäkitalo" <t.maekitalo@epgmbh.de> To: "Tim Barnard" <tbarnard@povn.com> Sent: Sunday, January 20, 2002 10:53 AM Subject: Re: [GENERAL] Clarification question > Hi, > > You could use a join too. It will be simpler to optimize it: > > select attname > from pg_attribute join pg_class on attrelid = oid > where relname = '<insert table name here>' > and attnum > 0 > > Tommi > > ... > > >>>select attname from pg_attribute > >>> where attrelid= > >>> (select relfilenode from pg_class > >>> where relname like <insert table name here>) > >>> and attnum > 0 > >>> > ... > > >> > >>select attname from pg_attribute > >> where attrelid= > >> (select oid from pg_class > >> where relname = '<insert table name here>') > >> and attnum > 0 > >> > > >