pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Select all table column names for a specified tablename (per the system catalogs) 3+ messages / 3 participants [nested] [flat]
* Select all table column names for a specified tablename (per the system catalogs) @ 2008-09-22 22:06 kevin kempter <kevin@kevinkempterllc.com> 0 siblings, 1 reply; 3+ messages in thread From: kevin kempter @ 2008-09-22 22:06 UTC (permalink / raw) To: pgsql-admin 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... ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Select all table column names for a specified tablename (per the system catalogs) @ 2008-09-22 22:29 Joshua Drake <jd@commandprompt.com> parent: kevin kempter <kevin@kevinkempterllc.com> 0 siblings, 1 reply; 3+ messages in thread From: Joshua Drake @ 2008-09-22 22:29 UTC (permalink / raw) To: kevin kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-admin 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Select all table column names for a specified tablename (per the system catalogs) @ 2008-09-23 03:37 Tom Lane <tgl@sss.pgh.pa.us> parent: Joshua Drake <jd@commandprompt.com> 0 siblings, 0 replies; 3+ messages in thread From: Tom Lane @ 2008-09-23 03:37 UTC (permalink / raw) To: Joshua Drake <jd@commandprompt.com>; +Cc: kevin kempter <kevin@kevinkempterllc.com>; pgsql-admin 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-09-23 03:37 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2008-09-22 22:06 Select all table column names for a specified tablename (per the system catalogs) kevin kempter <kevin@kevinkempterllc.com> 2008-09-22 22:29 ` Joshua Drake <jd@commandprompt.com> 2008-09-23 03:37 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox